Datatable to json with grouping

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?

image

[
  {
    "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"
  }
]
1 Like

HI @c.ciprian

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

Regards

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…

1 Like

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; }
    }
}
  1. Install it from the manage packages before running the code, I have included the package in the above attached zip

nmnithinkrishna_dependencies

  1. 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

  1. 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

image

How the DT is getting manipulated

image

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

3 Likes

@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:
grafik
grafik
grafik

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)

1 Like

Thankyou both for the help. I really must learn LINQ :slight_smile:

1 Like

@c.ciprian

1 Like

Cool @c.ciprian :slightly_smiling_face::+1:

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.