How to convert Dynamic JSON

Question

I am trying to convert some data from one JSON format to another, but running into to some issues.

[
[{"Key":"entity_id","Value":"1"},{"Key":"CustomerName","Value":"Test1"},{"Key":"AccountNumber","Value":"ACC17-001"},{"Key":"CustomerType","Value":"Direct Sale"}],
[{"Key":"entity_id","Value":"2"},{"Key":"CustomerName","Value":"Test2"},{"Key":"AccountNumber","Value":"ACC17-002"},{"Key":"CustomerType","Value":"Direct Sale"}],
[{"Key":"entity_id","Value":"3"},{"Key":"CustomerName","Value":"Test3"},{"Key":"AccountNumber","Value":"ACC17-003"},{"Key":"CustomerType","Value":"Direct Sale"}],
[{"Key":"entity_id","Value":"4"},{"Key":"CustomerName","Value":"Test4"},{"Key":"AccountNumber","Value":"ACC17-004"},{"Key":"CustomerType","Value":"Direct Sale"}],
[{"Key":"entity_id","Value":"5"},{"Key":"CustomerName","Value":"Test5"},{"Key":"AccountNumber","Value":"ACC17-005"},{"Key":"CustomerType","Value":"Invoice"}],
[{"Key":"entity_id","Value":"6"},{"Key":"CustomerName","Value":"Test6"},{"Key":"AccountNumber","Value":"ACC17-006"},{"Key":"CustomerType","Value":"Invoice"}]
]

Into this:

[
{"entity_id":"1","CustomerName":"Test1","AccountNumber":"ACC17-001","CustomerType":"Direct Sale"},
{"entity_id":"2","CustomerName":"Test2","AccountNumber":"ACC17-002","CustomerType":"Direct Sale"},
{"entity_id":"3","CustomerName":"Test3","AccountNumber":"ACC17-003","CustomerType":"Direct Sale"},
{"entity_id":"4","CustomerName":"Test4","AccountNumber":"ACC17-004","CustomerType":"Direct Sale"},
{"entity_id":"5","CustomerName":"Test5","AccountNumber":"ACC17-005","CustomerType":"Invoice"},
{"entity_id":"6","CustomerName":"Test6","AccountNumber":"ACC17-006","CustomerType":"Invoice"}
]

The first data source is a dynamic sql query...and i need to generate a custom object with the columns of that sql query as the properties of the object and then encode it into JSON for the webservice to reply with.

I am using c# as the intermediate layer.

dbc · Answer 1 · 2018-05-19T03:39:12.350

You can perform the required transformation by installing json.net as shown in How to install JSON.NET using NuGet? and then using LINQ to JSON to parse and transform your input JSON without making any assumptions about the precise keys and values present.

If inputJson is the JSON string you receive from your SQL query, then the following code does the job:

// Deserialize the input JSON as an array of arrays of JSON objects
var inputArray = JsonConvert.DeserializeObject<JObject [][]>(inputJson);

// Convert each inner array to a JSON object whose property names come from the nested objects' Key values and property values come from the nested objects' Value values:
var outputArray = inputArray.Select(a => new JObject(a.Select(o => (new JProperty((string)o["Key"], o["Value"])))));

// And re-serialize to JSON.
var outputJson = JsonConvert.SerializeObject(outputArray, Formatting.Indented);

Sample working .Net fiddle here.

Notes:

I initially deserialize to a JObject [][] to ensure the inputs are, in fact, an array of array of objects.
Initially deserializing to a KeyValuePair<string, JToken> [][] also works, as shown here.

How to convert Dynamic JSON

1 Answers1