flattening json structure with arrays to multiple flat objects without arrays

Question

I'm not sure whether did I describe the problem in subject 100% correctly, but I believe that the examples will do the trick.

I have JSON structure like below (note: there is small chance that this might change, so i need to lean forward to generic solution)

One invoice with multiple line items:

{
    "contactName": "Company",
    "lineItems": [
     {
        "quantity": 7.0,
        "description": "Beer No* 45.5 DIN KEG"
     },
     {
        "quantity": 2.0,
        "description": "Beer Old 49.5 DIN KEG"
     }
     ],
    "invoiceNumber": "C6188372"
}

And this is the wanted result data structure (multiple invoices with duplicated data and different line item info):

[{
    "contactName": "Company",
    "quantity": 7.0,
    "description": "Beer No* 45.5 DIN KEG"
    "invoiceNumber": "C6188372"
},{
    "contactName": "Company",
    "quantity": 2.0,
    "description": "Beer Old 49.5 DIN KEG"
    "invoiceNumber": "C6188372"
}]

So each "line item" from "invoice" should "result" in new invoice with duplicated other elements.

Small variations around result data structure are accepted, i can adjust my code around it. I've been spinning around using several similar questions such as:

C# flattening json structure
Flatten an array of objects that may contain arrays
How can I flatten a collection of objects (which in turn contain collections)?
Generically Flatten Json using c# (etc). i believe that this is the closest solution? but unsure if there is some better approach

For more background, i need this for CSV export. So result set should be two rows in generated CSV.

Any hints/tips are much appreciated. Thanks.

Just to clarify are you consuming the first JSON structure and wanting to restructure it to the second structure? or are your C# Classes generating the first JSON structure and you instead want it to generate the second JSON structure (Effectively the first JSON structure should never exist) — Skintkingle
– Skintkingle, Commented Aug 28, 2018 at 12:19
@Skintkingle First is correct - I'm consuming 1st JSON structure and i need to restructure it to 2nd structure. — Ivan Kalafatić
– Ivan Kalafatić, Commented Aug 28, 2018 at 12:23
Do you have a C# Class at the moment that deserializes nicely from the first example? if so could you supply that Class in the question. :) — Skintkingle
– Skintkingle, Commented Aug 28, 2018 at 12:27
@Skintkingle I'm working with object/JObject/JToken etc due to dynamic data structure, so (sadly) I do not have fixed c# class — Ivan Kalafatić
– Ivan Kalafatić, Commented Aug 28, 2018 at 12:30
So the JSON you are reading in has an unknown data structure? or it just conditionally sometimes doesn't supply a parameter here or there? — Skintkingle
– Skintkingle, Commented Aug 28, 2018 at 12:32

DavidG · Accepted Answer · 2018-08-28 12:33:45Z

5

You could do it with a function like this:

//Pass in the name of the array property you want to flatten
public string FlattenJson(string input, string arrayProperty)
{
    //Convert it to a JObject
    var unflattened = JsonConvert.DeserializeObject<JObject>(input);

    //Return a new array of items made up of the inner properties
    //of the array and the outer properties
    var flattened = ((JArray)unflattened[arrayProperty])
        .Select(item => new JObject(
            unflattened.Properties().Where(p => p.Name != arrayProperty), 
            ((JObject)item).Properties()));

    //Convert it back to Json
    return JsonConvert.SerializeObject(flattened);
}

And call it like this:

var flattenedJson = FlattenJson(inputJson, "lineItems");

answered Aug 28, 2018 at 12:33

DavidG

120k13 gold badges232 silver badges239 bronze badges

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

1 Comment

Ivan Kalafatić Over a year ago

I have tried this, and i think that this will do the trick. I will need to expand it to be recursive and to detect which properties are arrays so that I do not need to explicitly write "lineItems" in code. Anyhow, I have accepted your answer as it gave me idea how to proceed. Thanks

Skintkingle · Accepted Answer · 2018-08-28 12:44:14Z

2

You could use a custom JsonConverter if you are able to Deserialize/Serialize into a strongly typed class. Invoice information I would imagine should be in some semi-structured object so this should be doable:

public class Invoice
{
    public string ContactName { get; set; }
    public List<Item> LineItems { get; set; } = new List<Item>();
    public string InvoiceNumber { get; set; }
}

public class Item
{
    public double Quantity { get; set; }
    public string Description { get; set; }
}

And then with the JsonConverter you can flatten it based upon the Items (Or any other property/properties you may want)

public class InvoiceFlattener : JsonConverter
{
    public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
    {
        var obj = value as Invoice;
        if (obj == null)
        {
            return;
        }

        writer.WriteStartArray();

        foreach (var item in obj.LineItems)
        {
            writer.WriteStartObject();
            writer.WritePropertyName(nameof(obj.ContactName));
            writer.WriteValue(obj.ContactName);
            writer.WritePropertyName(nameof(item.Quantity));
            writer.WriteValue(item.Quantity);
            writer.WritePropertyName(nameof(item.Description));
            writer.WriteValue(item.Description);
            writer.WritePropertyName(nameof(obj.InvoiceNumber));
            writer.WriteValue(obj.InvoiceNumber);
            writer.WriteEndObject();
        }

        writer.WriteEndArray();
    }

    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        throw new NotImplementedException();
    }

    public override bool CanConvert(Type objectType)
    {
        return objectType == typeof(Invoice);
    }
}

And to use this Converter you supply it when Serializing

        var invoice = JsonConvert.DeserializeObject<Invoice>(inputJson);
        var outputJson = JsonConvert.SerializeObject(invoice, new InvoiceFlattener());

As you have probably worked out this converter doesn't work when deserializing but if this is a requirement you can write the logic in the ReadJson converter method. The downside to this is you will be required to maintain the converter should the structure of the Invoice class ever change. But it keeps us in a strongly typed world

answered Aug 28, 2018 at 12:44

Skintkingle

1,5794 gold badges16 silver badges30 bronze badges

3 Comments

Ivan Kalafatić Over a year ago

unfortunately, i do not live in strongly typed world in this scenario - input data structure is "dictated" by external Json Schema which can be modified at any given time, and i need to adapt to it :(

Skintkingle Over a year ago

In that case I hope the property that contains your list of lineItems doesn't change! DavidGs answer is probably what you want then.

Ivan Kalafatić Over a year ago

I just wrote a comment below his answer: I will expand his answer to be bit more generic, (to detect arrays) so that i do not need to hardcode "lineItems" string in the code. Thanks for your assistance though.

Cinchoo · Accepted Answer · 2018-09-04 00:59:11Z

2

With external lib Cinchoo ETL - an open source library, you can convert your JSON to expected CSV format with few lines of code

string json = @"{
    ""contactName"": ""Company"",
    ""lineItems"": [
     {
        ""quantity"": 7.0,
        ""description"": ""Beer No* 45.5 DIN KEG""
     },
     {
        ""quantity"": 2.0,
        ""description"": ""Beer Old 49.5 DIN KEG""
     }
     ],
    ""invoiceNumber"": ""C6188372""
}";

StringBuilder sb = new StringBuilder();
using (var p = ChoJSONReader.LoadText(json))
{
    using (var w = new ChoCSVWriter(sb)
        .WithFirstLineHeader()
        )
        w.Write(p
            .SelectMany(r1 => ((dynamic[])r1.lineItems).Select(r2 => new
            {
                r1.contactName,
                r2.quantity,
                r2.description,
                r1.invoiceNumber
            })));
}
Console.WriteLine(sb.ToString());

Output CSV:

contactName,quantity,description,invoiceNumber
Company,7,Beer No* 45.5 DIN KEG,C6188372
Company,2,Beer Old 49.5 DIN KEG,C6188372

Hope it helps.

answered Sep 4, 2018 at 0:59

Cinchoo

6,3562 gold badges21 silver badges36 bronze badges

1 Comment

Ivan Kalafatić Over a year ago

In meantime, I've done what I needed using DavidGs answer, however thanks for info! I was not aware of this library. I'll check it out.

Collectives™ on Stack Overflow

flattening json structure with arrays to multiple flat objects without arrays

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related