Is there a way to fill in missing properties in JSON objects across an array?

Question

I'm using JSON.NET in c# to reach out to an API that provides JSON as the output.

I print that output to the console as CSV so that it can be consumed by powershell convertfrom-csv, format-table, select-object and so on.

I've got a decent generic method for printing all kinds of JSON as CSV and it works almost all the time.

But, in some cases, I get JSON like this

{ 
    "result": 
    [ 
        { "product": "SP1", "version": "6.0" }, 
        { "product": "SP1 hf 40", "version": "6.0.40" }, 
        { "product": "SP1 hf 50", "version": "6.0.50" }, 
        { "version": "6.0.100" } 
    ] 
}

Where the last item from result is missing the product property entirely.

Is there a way to iterate over the items in result and put in the missing properties such that the output is:

{ 
    "result": 
    [ 
        { "product": "SP1", "version": "6.0" }, 
        { "product": "SP1 hf 40", "version": "6.0.40" }, 
        { "product": "SP1 hf 50", "version": "6.0.50" }, 
        { "product": "", "version": "6.0.100" } 
    ] 
}

This will allow me to continue using the generic method for printing the JSON output as a CSV.

One idea I had would be to iterate through the result array and find the element that had the most properties and use that as the basis for printing the CSV.

But that idea falls pretty flat on its face because the number of properties doesn't necessarily guarantee all the properties across the entire set.

I think I almost need a UNION type operation that will find all the properties in all the elements of an array.

Sorry if this is a really dumb question but I was just wondering if it was possible.

stackoverflow.com/questions/11132288/… Does this help?

csprabala
– csprabala

2014-10-23 04:22:45 +00:00
Commented Oct 23, 2014 at 4:22 — csprabala
– csprabala, Commented Oct 23, 2014 at 4:22

BnWasteland · Accepted Answer · 2014-10-23 05:52:13Z

2

Sure, you can do it. There are a number of approaches that would work. For example, you could simply do 2 passes. On the first pass you build up a complete list of properties, and on the second pass output to csv with the full headers.

No matter how you attack it though, this normalization will not do well when streaming.

I'd also like to point out that Powershell 3.0 and above come with ConvertFrom-Json. http://technet.microsoft.com/en-us/library/hh849898.aspx

answered Oct 23, 2014 at 5:52

BnWasteland

2,1191 gold badge18 silver badges14 bronze badges

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

1 Comment

anoopb Over a year ago

I like ConvertFrom-Json a lot but I'm having trouble figuring out how I can instantiate that in c# to convert my json output within the code itself. Perhaps I should ask another question about that. Thanks!

Brian Rogers · Accepted Answer · 2014-10-23 15:02:38Z

Yes, it is possible to fill in missing properties in the JSON. Here is an example using a 2-pass approach as suggested by @BnWasteland:

class Program
{
    static void Main(string[] args)
    {
        string json = @"
        {
            ""result"" : 
            [
                { ""a"" : ""a1"" },
                { ""b"" : ""b1"" },
                { ""c"" : ""c1"", ""d"" : ""d1"", ""a"" : ""a2"" },
                { ""a"" : ""a3"", ""d"" : ""d2"" },
                { ""b"" : ""b2"", ""c"" : ""c2"" },
            ]
        }";

        JObject jo = JObject.Parse(json);
        JArray ja = FillMissingProperties((JArray)jo["result"]);
        jo["result"] = ja;
        Console.WriteLine(jo.ToString());
    }

    public static JArray FillMissingProperties(JArray array)
    {
        // find all distinct property names across all objects in the array
        ISet<string> names = new SortedSet<string>();
        foreach (JObject obj in array.Children<JObject>())
        {
            foreach (JProperty prop in obj.Properties())
            {
                names.Add(prop.Name);
            }
        }

        // copy objects to a new array, adding missing properties along the way
        JArray arrayOut = new JArray();
        foreach (JObject obj in array.Children<JObject>())
        {
            JObject objOut = new JObject();
            foreach (string name in names)
            {
                JToken val = obj[name];
                if (val == null)
                {
                    val = new JValue("");
                }
                objOut.Add(name, val);
            }
            arrayOut.Add(objOut);
        }

        return arrayOut;
    }
}

Output:

{
  "result": [
    {
      "a": "a1",
      "b": "",
      "c": "",
      "d": ""
    },
    {
      "a": "",
      "b": "b1",
      "c": "",
      "d": ""
    },
    {
      "a": "a2",
      "b": "",
      "c": "c1",
      "d": "d1"
    },
    {
      "a": "a3",
      "b": "",
      "c": "",
      "d": "d2"
    },
    {
      "a": "",
      "b": "b2",
      "c": "c2",
      "d": ""
    }
  ]
}

Collectives™ on Stack Overflow

Is there a way to fill in missing properties in JSON objects across an array?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related