Generically Flatten Json using c#
You can use Json.Net's LINQ-to-JSON API to parse the data into a JToken
structure. From there, you can use a recursive helper method to walk the structure and flatten it to a Dictionary<string, object>
where the keys are the "path" to each value from the original JSON. I would write it something like this:
public class JsonHelper
{
public static Dictionary<string, object> DeserializeAndFlatten(string json)
{
Dictionary<string, object> dict = new Dictionary<string, object>();
JToken token = JToken.Parse(json);
FillDictionaryFromJToken(dict, token, "");
return dict;
}
private static void FillDictionaryFromJToken(Dictionary<string, object> dict, JToken token, string prefix)
{
switch (token.Type)
{
case JTokenType.Object:
foreach (JProperty prop in token.Children<JProperty>())
{
FillDictionaryFromJToken(dict, prop.Value, Join(prefix, prop.Name));
}
break;
case JTokenType.Array:
int index = 0;
foreach (JToken value in token.Children())
{
FillDictionaryFromJToken(dict, value, Join(prefix, index.ToString()));
index++;
}
break;
default:
dict.Add(prefix, ((JValue)token).Value);
break;
}
}
private static string Join(string prefix, string name)
{
return (string.IsNullOrEmpty(prefix) ? name : prefix + "." + name);
}
}
Using this DeserializeAndFlatten
method with your JSON you would end up with key-value pairs like this:
appointmentid: 4
policyid: 1
guid: 00000000-0000-0000-0000-000000000000
number: 1234567890
ampm: false
date: 9/8/2015 12:00:00 AM
vehicle.id: 1
vehicle.guid: 00000000-0000-0000-0000-000000000000
vehicle.make:
vehicle.model:
installer.installerid: 1
installer.name: Installer 1
installer.contact: qwerty
installer.qascore: 0
installer.address1: qwerty
installer.address2: qwerty
installer.address3:
installer.address4:
installer.city: qwertyu
installer.county: qwertyu
installer.postcode: asdfghj
installer.country: GB
installer.email: asdfghj
installer.web: asdfghjk
installer.archived: False
installations.0.installationid: 6
installations.0.installationstatus.installationstatusid: 4
installations.0.installationstatus.installationstatus: FAIL
installations.0.isactive: True
installations.1.installationid: 7
installations.1.installationstatus.installationstatusid: 1
installations.1.installationstatus.installationstatus: NEW
installations.1.isactive: False
archived: False
If you're looking to make the keys more human friendly, you could use a little string manipulation to cut them down. Maybe something like this:
var dict = JsonHelper.DeserializeAndFlatten(json);
foreach (var kvp in dict)
{
int i = kvp.Key.LastIndexOf(".");
string key = (i > -1 ? kvp.Key.Substring(i + 1) : kvp.Key);
Match m = Regex.Match(kvp.Key, @"\.([0-9]+)\.");
if (m.Success) key += m.Groups[1].Value;
Console.WriteLine(key + ": " + kvp.Value);
}
That would give you this output instead:
appointmentid: 4
policyid: 1
guid: 00000000-0000-0000-0000-000000000000
number: 1234567890
ampm: false
date: 9/8/2015 12:00:00 AM
id: 1
guid: 00000000-0000-0000-0000-000000000000
make:
model:
installerid: 1
name: Installer 1
contact: qwerty
qascore: 0
address1: qwerty
address2: qwerty
address3:
address4:
city: qwertyu
county: qwertyu
postcode: asdfghj
country: GB
email: asdfghj
web: asdfghjk
archived: False
installationid0: 6
installationstatusid0: 4
installationstatus0: FAIL
isactive0: True
installationid1: 7
installationstatusid1: 1
installationstatus1: NEW
isactive1: False
archived: False
But note, with this arrangement, you have lost some context: for example, you can see that there are now two identical archived
keys, whereas in the original JSON they were distinct because they appeared in different parts of the hierarchy (installer.archived
vs. archived
). You will need to figure out how to deal with that problem on your own.
Fiddle: https://dotnetfiddle.net/gzhWHk
Using the library Json.Net You could use the JSONPath $..*
to get all members of the JSON structure and filter out the ones with no children to skip the container properties.
e.g.
var schemaObject = JObject.Parse(schema);
var values = schemaObject
.SelectTokens("$..*")
.Where(t => !t.HasValues)
.ToDictionary(t => t.Path, t => t.ToString());