Deserialize JSON array of values in C#

Question

I've got some difficulties with this json script:

{
  "insured_agent_flag": "a",
  "id": "1",
  "agent": {
    "fullName": "John Travolta",
    "mobileNumberPdf": "+987654321",
    "mobileNumber": "",
    "identityCard": {
      "identityCardExpirationDate": null
    },
    "secondIdentityCard": {
      "identityCardExpirationDate": null
    },
    "notes": {},
    "sign": "ADVANCED"
  },
  "basicData": {
          "personType": "PERSON",
          "agreeWithCompleteAnalysis": false,
          "investmentInterest": false
  },
  "nonOfferedProducts": [
    "PROD_A",
    "PROD_B",
    "PROD_C"
  ]
}

I would like to get some parameters from this script and put it into sql server table. In order to do that, I used and transformed a C# script shared by https://mycontraption.com:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Web.Script.Serialization;
using Microsoft.SqlServer.Dts.Pipeline;

namespace SC_c7e2d8c3918d46a5a07a1b438ddc7642
{
public class BasicData
{
    public string agreeWithCompleteAnalysis { get; set; }
    public string inOtherSystem { get; set; }
    public string investmentInterest { get; set; }
}

public class ParentObject
{
    public BasicData BasicData { get; set; }
    public int id { get; set; }
    public string insured_agent_flag { get; set; }
    public IEnumerable<string> NonOfferedProducts { get; set; }
}

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        JavaScriptSerializer js = new JavaScriptSerializer();

        // Give the input column a variable to make it easier to reference.
        BlobColumn combinedColumn = Row.parameterscon;

        // Convert from blob to string
        string reviewConverted = System.Text.Encoding.ASCII.GetString(combinedColumn.GetBlobData(0, Convert.ToInt32(combinedColumn.Length)));

        // Deserialize the string
        ParentObject obj = js.Deserialize<ParentObject>(reviewConverted);


        var rows = obj.NonOfferedProducts.ToList();

        Row.agreeWithCompleteAnalysis = obj.BasicData.agreeWithCompleteAnalysis;
        Row.inOtherSystem = obj.BasicData.inOtherSystem;
        Row.investmentInterest = obj.BasicData.investmentInterest;
        Row.projectionid = obj.id;
        Row.insuredagentflag = obj.insured_agent_flag;

        //Row.nonOfferedProducts =

    }
}
}

For 'standard' objects it works fine, but there is a problem with array "nonOfferedProducts". After compiling I get an error: „object reference not set to an instance of an object”.

Here are my questions: 1. How should I handle 'nonOfferedProducts' array in C# script? 2. Why do I get foregoing error? 3. Unfortunately there exists a possibility, that json scripts would have some errors, like missing braces. How should I handle that?

Thank you!

Thanks a lot for your answers. According to your comments I'll try to give you more explanations: 1. The json script I have added in this post - it's only small part of whole script. In complete script there is a lot of different parameters. What is more, my C# code should scan about 40.000 json scripts (stored in sql server table in one column). These scripts has got similiar structure - but not the same. So I thought about C# resolution, that will be searching for the parameters that I need. For json scripts without these parameters the c# code will put nulls to the right output columns.

Here are my output columns: -agreeWithCompleteAnalysis -inOtherSystem -investmentInterest -projectionId -insuredAgentFflag -nonOfferedProducts

I understood, that structure of my classes were wrong - I'll improve that. But I've got one doubt - is it possible to prepare c# code structure, that will handle only these parameters I need?

And finally, I would like to put the results into my database. For example if nonOfferedProducts property will have 3 values (not always!), I'd like to send to my database table 3 records (3 different values for nonOfferedProducts column and 3 the same values for the rest columns -agreeWithCompleteAnalysis, inOtherSystem etc).

I hope that will be clear now. Thanks a lot for your help!

J

Please edit your code to add COMMENTS that are an english description of the algorithm you're expecting your loop to carry out - at the moment it makes no logical sense and your intent cannot hence be inferred from what is written — Caius Jard
– Caius Jard, Commented Dec 15, 2017 at 11:15
your c# class doesn't match the JSON model - basicData is an object within the main object. agreeWithCompleteAnalysis is a property of this object. nonOfferedProducts is an array property of the parent object. I'm not sure how the deserialization handles this, but I'll assume that it's picking up the basicData object only which doesn't have the nonOfferedProducts property, so it's null. The basic answer though is simply check if nonOfferedProducts is null before referencing it - but I think your problem is really this mis-match between the JSON and your class — GPW
– GPW, Commented Dec 15, 2017 at 11:45
To answer the second point, if the JSON is malformed, you will either have to reject it, or write your own parser from scratch to pull out the values you want regardless of whether the appropriate bits are there. Personally I think you should simply insist on consistent, valid data or you're asking for trouble. JSON that is "missing" braces is by definition not JSON - just a bunch of text that looks like it. — GPW
– GPW, Commented Dec 15, 2017 at 11:47
Thanks for your comments! I've added more explanations and algorithm description in my post. — johnowens
– johnowens, Commented Dec 15, 2017 at 14:58
If your C# class has a property that isn't mentioned in the JSON you attempt to deserialize, then that property will be left as its default value (null for reference types/objects, and usually 0 (zero) for ints/decimals/etc.) if the JSON has a property that isn't in your c#, then it will be ignored by default. — GPW
– GPW, Commented Dec 15, 2017 at 16:05

Praveen Kumar N · Accepted Answer · 2017-12-15 12:53:57Z

4

Use https://quicktype.io and paste json, it will generate c# model and serializer code.

answered Dec 15, 2017 at 12:53

Praveen Kumar N

741 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GPW Over a year ago

upvote for this being a cool little tool with which I was unfamiliar - very handy and I shall bookmark it

GPW · Accepted Answer · 2017-12-15 12:11:20Z

As I said in my comment, your c# model doesn't match the JSON object.

If the model was made up of various nested objects to better reflect the actual JSON then you'll have more luck:

public class IdentityCard
{
    public DateTime? IdentityCardExpirationDate { get; set; }
}

public class Notes
{
    //No idea what should be in here... 
}
public class BasicData
{
    public string PersonType { get; set; }
    public bool AgreeWithCompleteAnalysis { get; set; }
    public bool InvestmentInterest { get; set; }
}

public class Agent
{
    public string FullName { get; set; }
    public string MobileNumberPdf { get; set; }
    public string MobileNumber { get; set; }
    public IdentityCard IdentityCard { get; set; }
    public IdentityCard SecondIdentityCard { get; set; }
    public Notes Notes { get; set; }
    public string Sign { get; set; }
}

//Note: THIS is the actual class that matches the JSON sample given.
public class ParentObject
{
    public string insured_agent_flag { get; set; }
    public int Id { get; set; }
    public Agent Agent { get; set; }
    public BasicData BasicData { get; set; }
    public IEnumerable<string> NonOfferedProducts { get; set; }
}

Once the model is correct, then Deserialization works fine for me with the given example (I did this in a unit test, but assuming your string matches your example this should be fine)

//get json
string json = @"
    {
        ""insured_agent_flag"": ""a"",
        ""id"": ""1"",
        ""agent"": {
        ""fullName"": ""John Travolta"",
        ""mobileNumberPdf"": ""+987654321"",
        ""mobileNumber"": """",
        ""identityCard"": {
            ""identityCardExpirationDate"": null
        },
        ""secondIdentityCard"": {
            ""identityCardExpirationDate"": null
        },
        ""notes"": {},
        ""sign"": ""ADVANCED""
        },
        ""basicData"": {
                ""personType"": ""PERSON"",
                ""agreeWithCompleteAnalysis"": false,
                ""investmentInterest"": false
        },
        ""nonOfferedProducts"": [
        ""PROD_A"",
        ""PROD_B"",
        ""PROD_C""
        ]
    }";

var js = new JavaScriptSerializer();
ParentObject obj = js.Deserialize<ParentObject>(json);

//do things...
var rows = obj.NonOfferedProducts.ToList();
Assert.AreEqual(3, rows.Count);
Assert.AreEqual("PROD_A", rows.First());

The asserts pass - This code happily gets the list of strings in the NonOfferedProducts property with the given example.

Obviously if you cannot rely on the consistency of the JSON (either structure or how well-formed it is) then you'll have problems, but that's a different issue.

Thank you for your explanation. I've improved structure of my classes - the c# code is updated in my post. But I'm not sure if your code will resolve my problem. Could you take a look at additional description? Thank you!

Kannan · Accepted Answer · 2017-12-15 12:16:08Z

0

To answer your question no 2) you are getting the object reference error because the BasicDataClass.nonOfferedProducts is null and you are trying iterate over it , this may be a reason that you are sending the wrong json which JavaScriptSerializer is not able to deserilize.

your 3rd question you can always validate your json with json validators which are there online like https://jsonformatter.org/

answered Dec 15, 2017 at 12:16

Kannan

14 bronze badges

Collectives™ on Stack Overflow

Deserialize JSON array of values in C#

3 Answers 3

1 Comment

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Related