Lars335 Lars335 - 6 months ago 12
JSON Question

Good way to parse this dynamic JSON

I have JSON data that varies based on account settings. It has a "values" array that can contain zero or more arrays of field values and a columnNames array that gives the column name that goes with the values. Here is an example:

{
"values": [
[
"32999000000001",
"32999*****0001",
"Joe",
"Doe",
"joe@doe.com",
"1/1/1900",
null,
"1234",
null,
"222-111-2223",
null,
null,
null,
null,
"UNITED STATES",
null,
null,
"Acme, Inc."
],
[
"39999000000002",
"39999*****0002",
"Joe",
"Schmoe",
"joe@yahoo.com",
"1/1/1990",
null,
"333-555-4444",
null,
"123 Some Street",
null,
"Michigan",
null,
null,
"UNITED STATES",
null,
null,
"Acme, Inc."
],
],
"columnNames": [
"accountNumber",
"Acct Number",
"First Name",
"Last Name",
"Email Address",
"Date Of Hire",
"Anniversary Date",
"Phone Number",
"Other Phone Number",
"Address1",
"Address2",
"City",
"State",
"Postal Code",
"Country",
"Company",
"Club ID",
"Member Type"
]
}


I need to populate an array of business objects with this data (the business object has a property for each column name in the JSON data that I am interested in):

public class SearchResultRecord
{
public string AccountNumber { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string EmailAddress { get; set; }
public DateTime? DateOfHire { get; set; }
public DateTime? AnniversaryDate { get; set; }
public string PhoneNumber { get; set; }
public string OtherPhoneNumber { get; set; }
public string Address1 { get; set; }
public string Address2 { get; set; }
public string City { get; set; }
public string State { get; set; }
public string Zip { get; set; }
public string Country { get; set; }
public string Company { get; set; }
public string ClubId { get; set; }
public string MemberType { get; set; }
}


I am planning to use JSON.NET for this and I could create a custom attribute to decorate each property and then use reflection to do the mapping of the values to the right property, but I am hoping that there is an easier / cleaner way or perhaps something already built into JSON.NET that I can use.

What is a clean, good way to accomplish this?

Note that I do not have to use JSON.NET if there is a more suitable library out there.

Answer

this code will do the transformation for you:

var props = typeof(SearchResultRecord).GetProperties().ToDictionary(
                    x => x.Name, 
                    StringComparer.OrdinalIgnoreCase);
var jss = new JavaScriptSerializer();
var data = (Dictionary<string,object>)jss.DeserializeObject(json); 
var cols = ((object[])data["columnNames"]).OfType<string>()
                                          .Select((x,index) => new{index,name=x.Replace(" ","")})
                                          .Where (x => props.ContainsKey(x.name))
                                          .Select (x => new {x.index,prop=props[x.name]}).Dump();
var rows = ((object[])data["values"]).OfType<object[]>().ToArray();

foreach (var row in rows)
{
    var rowdict = cols.Where (c => row[c.index] != null).ToDictionary(c => c.prop.Name, c => row[c.index]);
    var rowjson = jss.Serialize(rowdict);
    jss.Deserialize<SearchResultRecord>(rowjson).Dump();
}
Comments