Having issues parsing a JSON response with separate columns and rows

Question

I am having issues figuring out how to parse a JSON response that I am getting back from a 3rd party. I need to convert from the following JSON (a simplified example of what I am getting back) to a C# class. The main issue is that the columns may be in a different order, have extra fields, or missing ones (Which should just end up as null values).

{
    "columns": [
        { "id": { "type": "Numeric", "nullable": false } },
        { "name": { "type": "Text", "nullable": false } },
        { "description": { "type": "Text", "nullable": true } },
        { "last_updated": { "type": "DateTime", "nullable": false } }
    ],
    "rows": [
        [1, "foo", "Lorem ipsum", "2016-10-26T00:09:14Z"],
        [4, "bar", null, "2013-07-01T13:04:24Z"]
    ]
}

The C# class for this example would be

public class Record
{
    public int id { get; set; }
    public string name { get; set; }
    public string description { get; set; }
    public DateTime? last_updated { get; set; }
}

I have tried using a custom json converter, but did not have much luck getting it to work with the separated metadata and values. Does anyone have any ideas on how to go about parsing this kind of data? Eventually there will be multiple "Record" types, which is why the response from the server can be dynamic.

Answer 1

Your question is similar to this recent one and can be solved using a similar converter:

public class RowColumnListConverter<T> : JsonConverter
{
    const string columnsKey = "columns";
    const string rowsKey = "rows";

    public override bool CanConvert(Type objectType)
    {
        if (!typeof(ICollection<T>).IsAssignableFrom(objectType))
            return false;
        // This converter is only implemented for read/write collections.  So no arrays.
        if (objectType.IsArray)
            return false;
        return true;
    }

    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        if (reader.TokenType == JsonToken.Null)
            return null;
        var list = existingValue as ICollection<T> ?? (ICollection<T>)serializer.ContractResolver.ResolveContract(objectType).DefaultCreator();
        var root = JObject.Load(reader);
        var rows = root.GetValue(rowsKey, StringComparison.OrdinalIgnoreCase).NullCast<JArray>();
        if (rows == null)
            return list;
        var columns = root.GetValue(columnsKey, StringComparison.OrdinalIgnoreCase).NullCast<JArray>();
        if (columns == null)
            throw new JsonSerializationException(columnsKey + " not found.");
        var columnNames = columns.Cast<JObject>().SelectMany(o => o.Properties()).Select(p => p.Name).ToArray();

        foreach (var row in rows)
        {
            if (row == null || row.Type == JTokenType.Null)
                list.Add(default(T));
            else if (row.Type == JTokenType.Array)
            {
                var o = new JObject(columnNames.Zip(row, (c, r) => new JProperty(c, r)));
                list.Add(o.ToObject<T>(serializer));
            }
            else
                throw new JsonSerializationException(string.Format("Row was not an array: {0}", row));
        }

        return list;
    }

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

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

public static class JsonExtensions
{
    public static TJTOken NullCast<TJTOken>(this JToken token) where TJTOken : JToken
    {
        if (token == null || token.Type == JTokenType.Null)
            return null;
        var typedToken = token as TJTOken;
        if (typedToken == null)
            throw new JsonSerializationException(string.Format("Token {0} was not of type {1}", token.ToString(Formatting.None), typeof(TJTOken)));
        return typedToken;
    }
}

Then use it like:

var list = JsonConvert.DeserializeObject<List<Record>>(json, new RowColumnListConverter<Record>());

Or

var list = JsonConvert.DeserializeObject<List<Record>>(json, new JsonSerializerSettings
                                                       {
                                                           Converters = { new RowColumnListConverter<Record>() },
                                                       });

The converter works by loading the outer object into a temporary JObject, then reformatting the "columns" and "rows" arrays into a more conventional list of objects for deserialization. Note that no attempt is made to use the type information in the "columns" list, it is simply assumed that the POCO members have the correct type. Also, WriteJson() is not implemented since there isn't enough information in the question to determine how to emit the type information for any possible column type; a full specification would be required.

Sample fiddle.

Answer 2

i think this approach is tricky somehow, but if you have to follow this, you can put order property for them to solve your order problem:

    {
    "columns": [
        { "id": { "type": "Numeric", "nullable": false, "order":1 } },
        { "name": { "type": "Text", "nullable": false, "order":2 } },
        { "description": { "type": "Text", "nullable": true, "order":3 } },
        { "last_updated": { "type": "DateTime", "nullable": false, "order":4 } }
    ],
    "rows": [
        [1, "foo", "Lorem ipsum", "2016-10-26T00:09:14Z"],
        [4, "bar", null, "2013-07-01T13:04:24Z"]
    ]
}

also for null value you can get a default value to recognize them and replace with null in your custom json converter.

the best structure for your data can be:

public class Column
{
    public string type { get; set; }
    public bool nullable { get; set; }
    public int order { get; set; }
}

public class Model
{
    public List<Dictionary<string, Column>> columns { get; set; }
    public List<List<string>> rows { get; set; }
}

you can convert your json to the a Model class directly.