Flatten Json & Ignore array index Using ChoETL

Question

I have 1 json file and these lines of code: Here's my code:

  using (var r = new ChoJSONReader("data.json")
                                .Configure(c => c.ThrowAndStopOnMissingField = true)
                                .Configure(c => c.DefaultArrayHandling = true)
                                .Configure(c => c.FlattenNode = true)
                                .Configure(c => c.IgnoreArrayIndex = false)
                                .Configure(c => c.NestedKeySeparator = '.')
                                .Configure(c => c.NestedColumnSeparator = '.')
                )
            {
                var dt = r.AsDataTable();
                Console.WriteLine(dt.DumpAsJson());
            }

My data.json file:

{
  "BrandId": "998877665544332211",
  "Categories": [
    "112233445566778899"
  ],
  "Contact": {
    "Phone": [
      {
        "Value": "12346789",
        "Description": {
          "vi": "Phone"
        },
        "Type": 1
      },
      {
        "Value": "987654321",
        "Description": {
          "vi": "Phone"
        },
        "Type": 1
      }
    ]
  }
}

After running this code, I got the output like this:

[
  {
    "BrandId": "998877665544332211",
    "Contact.Phone.0.Value": "12346789",
    "Contact.Phone.0.Description.vi": "Phone",
    "Contact.Phone.0.Type": 1,
    "Contact.Phone.1.Value": "987654321",
    "Contact.Phone.1.Description.vi": "Phone",
    "Contact.Phone.1.Type": 1,
    "Category0": "112233445566778899"
  }
]

The question here is how can I get some kind of output json without "0" at the flattened key node Expected output:

[
  {
    "BrandId": "998877665544332211",
    "Contact.Phone.Value": "12346789",
    "Contact.Phone.Description.vi": "Phone",
    "Contact.Phone.Type": 1,
    "Category": "112233445566778899"
  },
  {
    "BrandId": "998877665544332211",
    "Contact.Phone.Value": "987654321",
    "Contact.Phone.Description.vi": "Phone",
    "Contact.Phone.Type": 1,
    "Category": "112233445566778899"
  }
]

I've research by many ways but it's results doesn't as same as my expected result. Thanks for any kind of help

score 2 · Accepted Answer · answered Mar 22 '22 at 23:11

As your json is nested/complex in nature, you need to unpack and flatten into multiple simple data element rows using ChoETL/Linq as below.

ChoETLSettings.KeySeparator = '-';
using (var r = ChoJSONReader.LoadText(json)
       .WithField("BrandId")
       .WithField("Category", jsonPath: "Categories[0]", isArray: false)
       .WithField("Phone", jsonPath: "Contact.Phone[*]")
      )
{
    var dt = r.SelectMany(rec => ((IList)rec.Phone).OfType<dynamic>().Select(rec1 => 
                                                                    {
                                                                        dynamic ret = new ChoDynamicObject();
                                                                        ret["BrandId"] = rec.BrandId;
                                                                        ret["Contact.Phone.Value"] = rec1.Value;
                                                                        ret["Contact.Phone.Description.vi"] = rec1.Description.vi;
                                                                        ret["Contact.Phone.Type"] = rec1.Type;
                                                                        ret["Category"] = rec.Category;
                                                                        return ret;
                                                                    })).AsDataTable();
    
    dt.DumpAsJson().Print();
}

Sample fiddle: https://dotnetfiddle.net/PHK8LO

Flatten Json & Ignore array index Using ChoETL

1 Answers1