3

I'm getting an irregular JSON array from the Census Bureau's public api. The variable names are all in the first element, and I'm having trouble deserializing it.

http://api.census.gov/data/2014/pep/agesex?get=AGE,POP,SEX&for=us:*&DATE=7

gives me JSON like this:

[["AGE","POP","SEX","DATE","us"],
["0","3948350","0","7","1"],
["1","3962123","0","7","1"],
["2","3957772","0","7","1"],
["3","4005190","0","7","1"],
["4","4003448","0","7","1"],
["5","4004858","0","7","1"],
["6","4134352","0","7","1"],
["7","4154000","0","7","1"]]

I can successfully deserialize this using:

var test1 = JsonConvert.DeserializeObject<String[][]>(jsonStr);

However, I'm trying to deserialize it to a class like this:

public class TestClass
{
    public string AGE { get; set; }
    public string POP { get; set; }
    public string SEX { get; set; }
    public string DATE { get; set; }
    public string us { get; set; }
}

I'm trying to do this:

var test2 = JsonConvert.DeserializeObject<TestClass[]>(jsonStr);

But I'm getting the following exception:

An exception of type 'Newtonsoft.Json.JsonSerializationException' occurred in Newtonsoft.Json.dll but was not handled in user code

Additional information: Cannot create and populate list type TestClass. Path '[0]', line 1, position 2.

6
  • I'm pretty sure you'll have to deserialize it as an array of arrays, then convert it in to the format you want. Commented Mar 21, 2016 at 14:10
  • 2
    Here's an example of that: dotnetfiddle.net/Cr0aRL Commented Mar 21, 2016 at 14:25
  • 1
    @AndyJ your fiddle looks quite nice. Care to convert it into an answer here? Commented Mar 21, 2016 at 14:53
  • 1
    Wow @AndyJ. I forgot about dotnetfiddle. That's an extremely good solution. I agree with derpirscher, you should convert it to an answer. Commented Mar 21, 2016 at 15:03
  • @derpirscher I originally wrote that fiddle to use as an answer, but when I came back there was already two answers with roughly the same code so I just left a comment. However since there's demand for it I've written up the full answer with the extra detail and suggestions for future automation. Thanks for the kind words :) Commented Mar 21, 2016 at 15:08

5 Answers 5

6

There's two parts to this.

First is turning the JSON in to data usable in C#, and the second is turning that data in to nice objects.

Here's a working dotNetFiddle.net example of the following code: https://dotnetfiddle.net/Cr0aRL

Each row in your JSON is made up of an array of strings. So that's an array of an array of strings. In C# that can be written as string[][].

So to turn the JSON in to usable data with JSON.Net you can do:

    var json = "[[\"AGE\",\"POP\",\"SEX\",\"DATE\",\"us\"],[\"0\",\"3948350\",\"0\",\"7\",\"1\"],[\"1\",\"3962123\",\"0\",\"7\",\"1\"],[\"2\",\"3957772\",\"0\",\"7\",\"1\"],[\"3\",\"4005190\",\"0\",\"7\",\"1\"],[\"4\",\"4003448\",\"0\",\"7\",\"1\"],[\"5\",\"4004858\",\"0\",\"7\",\"1\"],[\"6\",\"4134352\",\"0\",\"7\",\"1\"],[\"7\",\"4154000\",\"0\",\"7\",\"1\"]]";
    var rawData = JsonConvert.DeserializeObject<string[][]>(json);

Next up is is turning that data in to objects.

The first row is the header, containing the column names, so we want to grab that, and then figure out the column index for each column name.

    var headerRow = rawData.First();    

    var ageIndex = Array.IndexOf(headerRow, "AGE");
    var popIndex = Array.IndexOf(headerRow, "POP");
    var sexIndex = Array.IndexOf(headerRow, "SEX");
    var dateIndex = Array.IndexOf(headerRow, "DATE");
    var usIndex = Array.IndexOf(headerRow, "us");

Now we have the indexes, we need to take each row, and convert it in to the appropriate object. I've used LINQ for this as it's very good at representing data processing in a clear way.

    var testData = rawData
        .Skip(1) //The first row is a header, not data
        .Select(dataRow => new TestClass()
        {
            AGE = dataRow[ageIndex],
            POP = dataRow[popIndex],
            SEX = dataRow[sexIndex],
            DATE = dataRow[dateIndex],
            us = dataRow[usIndex]
        });

Finally a bit of testing, to make sure you have the data you're expecting.

    //Get the second data row as an example
    var example = testData.Skip(1).First();

    //Output example POP to check value
    Console.WriteLine(example.POP);

Everything above is very manual.

You have to know what headers you expect, then you manually find the indexes, then you manually map the rows to objects.

It's quite possible for a simple use case that doing that is fine. But in larger and/or more complex systems you might want/need to automate those steps.

Automating those steps is possible, but is beyond the scope of this answer as how you approach it can depend on a lot of different factors.

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

Comments

3

You could make a custom JsonConverter to handle this conversion during deserialization. The conversion code is really not much different than other answers here, except that it is encapsulated into a separate class so that you don't muddy up your main code with the conversion details. From the point of view of your main code it "just works".

Here is how to write the converter:

public class TestClassArrayConverter : JsonConverter 
{
    public override bool CanConvert(Type objectType)
    {
        return (objectType == typeof(TestClass[]));
    }

    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        JArray table = JArray.Load(reader);
        TestClass[] items = new TestClass[table.Count - 1];
        for (int i = 1; i < table.Count; i++)
        {
            JArray row = (JArray)table[i];
            items[i - 1] = new TestClass
            {
                AGE = (string)row[0],
                POP = (string)row[1],
                SEX = (string)row[2],
                DATE = (string)row[3],
                us = (string)row[4]
            };
        }
        return items;
    }

    public override bool CanWrite
    {
        get { return false; }
    }

    public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
    {
        throw new NotImplementedException();
    }
}

And here is how you would use it:

var test2 = JsonConvert.DeserializeObject<TestClass[]>(jsonStr, new TestClassArrayConverter());

Fiddle: https://dotnetfiddle.net/68Q0KT

1 Comment

Nice! If you want the JSON.Net serialisation to "just work" then this is a great option.
2

You have to do the processing on your own, as there is no way the json deserializer can know, how to put the values into the respecitve variables.

If you know, this will be exactly this structure, you could for instance add an appropriate constructor

public TestClass(string[] values) {
    AGE = values[0]; 
    ...
}

to your class. Then serialize your result to array of arrays of string and then pass the inner arrays to your constructor.

var t1 = JsonConvert.DeserializeObject<string[][]>(jsonStr);
//skip the first entry, as this contains the headers
var t2 = t1.Skip(1).Select(x=> new TestClass(x));

If your structure varies, you'll have to write some more complicated mapping code.

1 Comment

Thanks. I was afraid of that. I was hoping there was a deserializer setting I wasn't aware of. I think yours is the simplest solution and gives me a couple opportunities for validation.
1

You will have to do some custom mapping as your Json does not have any naming conventions so you will have to work with the data in array and index formats. This will work:

var jsonStr = "[[\"AGE\",\"POP\",\"SEX\",\"DATE\",\"us\"], [\"0\",\"3948350\",\"0\",\"7\",\"1\"], [\"1\",\"3962123\",\"0\",\"7\",\"1\"], [\"2\",\"3957772\",\"0\",\"7\",\"1\"], [\"3\",\"4005190\",\"0\",\"7\",\"1\"], [\"4\",\"4003448\",\"0\",\"7\",\"1\"], [\"5\",\"4004858\",\"0\",\"7\",\"1\"], [\"6\",\"4134352\",\"0\",\"7\",\"1\"], [\"7\",\"4154000\",\"0\",\"7\",\"1\"]]";
var test2 = JsonConvert.DeserializeObject<string[][]>(jsonStr);
var test3 = test2.Select(x => new TestClass()
{
    AGE = x[0].ToString(),
    POP = x[1].ToString(),
    SEX = x[2].ToString(),
    DATE = x[3].ToString(),
    us = x[4].ToString()
}).ToList();

1 Comment

You should skip the first element of the array, as it contains the headers with variable names.
0
//test Case

using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Collections.Generic;
namespace ApiController.Test
{
    [TestClass]
    public class DownloadIrregularJsonStringObjects
    {
        string ApiKey => "YourPersonalCensusKey";

        /// <summary>
        /// You have to get your own ApiKey from the Census Website
        /// </summary>       
        [TestMethod]
        public void TestGetItem()
        {
        string url = $"http://api.census.gov/data/timeseries/healthins/sahie?get=NIC_PT,NAME,NUI_PT&for=county:*&in=state:*&time=2015&key={YourPersonalCensusKey}";
        string expected = "Autauga County, AL";
        IList<HealthData> actual = ApiController.DownloadIrregularJsonStringObjects.GetCensusHealthData(url);
        Assert.AreEqual(actual[0].NAME, expected);
    }
}
}

///Actual Assembly

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;

namespace ApiController
{
   public  class DownloadIrregularJsonStringObjects
    {
        public static IList<HealthData> GetCensusHealthData(string url)
        {
            var json = GetData(url);
            var rawData = JsonConvert.DeserializeObject<string[][]>(json);

            var headerRow = rawData.First();

            var nic_pt_Index = Array.IndexOf(headerRow, "NIC_PT");
            var name_Index = Array.IndexOf(headerRow, "NAME");
            var nui_pt_Index = Array.IndexOf(headerRow, "NUI_PT");

            IList<HealthData> retVal = new List<HealthData>();

            foreach (var r in rawData.Skip(1))
            {
                HealthData dataRow = new HealthData();
                dataRow.NIC_PT = r[nic_pt_Index];
                dataRow.NAME = r[name_Index];
                dataRow.NUI_PT = r[nui_pt_Index];
                retVal.Add(dataRow);                
            }
            return retVal;
        }

    private static string GetData(string url)
    {
        using (var w = new WebClient())
        {
            var jsonData = string.Empty;
            jsonData = w.DownloadString(url);

            return jsonData;
        }
    }
}
public class HealthData
{
    public string NIC_PT { get; set; }
    public string NAME { get; set; }
    public string NUI_PT { get; set; }       

}
}

1 Comment

please explain the answer a bit

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.