Datatable to json with grouping

[c.ciprian (Ciprian Constantinescu)]

Hi,

I need to convert this datatable (3 or more levels) into grouped json (id can be anything as long as there are unique, text goes into title, and the children under “subs”). Is there an easy way to do it?

[
  {
    "id": 1,
    "title": "Four Wheels",
    "subs": [
      {
        "id": 10,
        "title": "Car",
        "subs": [
          {
            "id": 100,
            "title": "Sport"
          },
          {
            "id": 101,
            "title": "SUV"
          }
        ]
      },
      {
        "id": 11,
        "title": "Truck"
      },
      {
        "id": 12,
        "title": "Transporter"
      }
    ]
  },
  {
    "id": 2,
    "title": "Two Wheels",
    "subs": [
      {
        "id": 20,
        "title": "Cycle"
      },
      {
        "id": 21,
        "title": "Motorbike",
        "subs": [
          {
            "id": 210,
            "title": "Racing"
          },
          {
            "id": 211,
            "title": "Chopper"
          }
        ]
      }
    ]
  },
  {
    "id": 2,
    "title": "Van"
  },
  {
    "id": 3,
    "title": "Bus"
  }
]

[Gokul001 (Gokul Balaji)]

HI @c.ciprian

Could you check on this thread hope this will help you.

Converting Data Table to Json after grouping

Hi guys, below shown is my datatable [image] Json string output should be: { “ID”:“1001”, “Users”:[ { “User Name”:“ABC”, “User Email”:“abc@@gmail.com”, “Employers”:[ { “Employer Name”:“Reliance”, “Employer Zip”:“12332” }, { “Employer Name”:“Airtel”, “Employer Zip”:“23232” } ] }, { “User Name”:“DEF”, “User Email”:“def@@gmail.com”, “Employers”:[ { “Employer Name”:“Idea”, “Employer Zip”:“32343” }, ] } ] }

Regards

[c.ciprian (Ciprian Constantinescu)]

Hi @Gokul001

The first thing to do was searching the forum and I got to that post as well. I understand how to build JArray or JObject, but I’m strugling with the actual processing of this csv to json…

[Nithinkrishna]

Hey @c.ciprian

Good day.

Please look at this work around for your requirement.

nmnithinkrishna_DTNestedGrouping.zip (21.2 KB)

What is in the workflow ?

1. It has a custom class library which will help us to create objects. This will help us to keep our approach clean and accurate

using System;

namespace CustomCategoryJObject
{
    public class Category
    {
        public int id { get; set; }

        public string title { get; set; }

        public Category[] subs { get; set; }
    }
}

2. Install it from the manage packages before running the code, I have included the package in the above attached zip

3. Coming to the logic, we are using Recursion by truncating the data table on every iteration

Read Excel into DT → Group DT by First Column → Iterate Each Unique Group Key → Remove the Key and Prepare a new DT for the grouped remaining data table → Assign to Category object → Repeats

4. Finally the Category object array formed is serialized into JSON string

For better understanding, you can please debug the code once to understand the flow in a much better way.

Input DT

How the DT is getting manipulated

Read the above from Left to Right

Output JSON

[
  {
    "id": 4,
    "title": "Four Wheels",
    "subs": [
      {
        "id": 5,
        "title": "Car",
        "subs": [
          {
            "id": 6,
            "title": "Sport",
            "subs": null
          },
          {
            "id": 7,
            "title": "SUV",
            "subs": null
          }
        ]
      },
      {
        "id": 9,
        "title": "Truck",
        "subs": []
      },
      {
        "id": 10,
        "title": "Transporter",
        "subs": []
      }
    ]
  },
  {
    "id": 12,
    "title": "Two Wheels",
    "subs": [
      {
        "id": 13,
        "title": "Cycle",
        "subs": []
      },
      {
        "id": 15,
        "title": "Motorbike",
        "subs": [
          {
            "id": 16,
            "title": "Racing",
            "subs": null
          },
          {
            "id": 17,
            "title": "Chopper",
            "subs": null
          }
        ]
      }
    ]
  },
  {
    "id": 18,
    "title": "Van",
    "subs": []
  },
  {
    "id": 20,
    "title": "Bus",
    "subs": []
  }
]

The automation workflow is completely dynamic and it should work for any data table size.

Hope this helps.

Thanks
#nK

[ppr (Peter Preuss)]

@c.ciprian

with some modifications and generalizations for having it extended already for deeper group (subs) levels have a look on following implementation.

Variables:

Helpers:

Part 1: Setting up the L1 Groups:

Part 2: Setting up the Subs:

Adding the Leafs:

Result:

Differences:

• IDS - A Path driven ID schema was used for the subs, with added counter for the leafs
• has assumption that in each L1 Axis each subgroup titles are unique

Kindly note:

• adoptions are possible for sure
• [0][1] TruckID can be brought to your ID system like

[0][1] = [0]+1*10 + [1] = 10 + 1 = 11 etc

Find starter help here:
DT_ToJSON_NestedLevels.xaml (26.4 KB)

[c.ciprian (Ciprian Constantinescu)]

Thankyou both for the help. I really must learn LINQ

[ppr (Peter Preuss)]

@c.ciprian

[HowTo] LINQ (VB.Net) Learning Catalogue

This topic references the different Learning HowTos related to LINQ. It will be updated on new published HowTos as well LINQ Learning: Topic LINK First Start with LINQ [HowTo] - First Start with LINQ (VB.Net) Exploring the LINQ universe [HowTo] - Exploring the LINQ Universe (VB.Net) Operator [HowTo] Overview Topic LINK Where [HowTo] LINQ - First Start # Where Operator Select work in progress Any [HowTo] LINQ – First Start # Any Operator All [HowTo] LINQ – First Start…

[Nithinkrishna]

Cool @c.ciprian