How to create a nested (parent-Child ) JSON response in c#?

Question

I am making a web service which will give a response in JSON format. I have fetched data from sql server and stored it in a Datatable. This is how the dt looks:-

id      Caption                 pid
F182    GLOBAL REPORTS          NULL
F184    software                NULL
F1227   LYB P&L Reports         F184
F1245   LYB Training            F184
F1239   test3                   F182
F1249   Paavan_Test_Reports     F184

Items in caption column which have pid as Null are the parents and they have children which have the same pid as their respective parent's id.

Eg: GLOBAL REPORTS has 1 child i.e test3 and software has 3 child.

I want the JSON response into the following format

[{ 
    id='F182',  
    caption='GLOBAL REPORTS',
    pid=null;
    items:[{
    id='F1239',
    caption='test3',
    pid='F182'}] 
    },
    { 
    id='F184',
    caption='software',
    pid='NULL',
    items:[{
    id='F1227',
    caption='LYB P&L Reports',
    pid='F184'
    },
    {
    id='F1245',
    caption='LYB Training',
    pid='F184'
    },
    { 
    id='F1249',
    caption='Paavan_Test_Reports',
    pid='F184'
    }
}]

i have made a class folder

    class folder
    {
    string id{get;set;}
    string pid{get;set;}
    string caption{get;set;}
   }

How can i get the array of objects items which will contain all the available child of a particular parent? I am new to Json objects and responses,

I have tried using the following method:

 var obj = dt.AsEnumerable()
                .GroupBy(r => r["pid"])
                .ToDictionary(g => g.Key.ToString(),
                              g => g.Select(r => new {
                              item = r["caption"].ToString(),
                              }).ToArray());
     var json = JsonConvert.SerializeObject(obj);

but this is giving me a simple json response without any hierarchy.

Answer 1

You need to build object hierarchy before serialization. To do so define a new property in your folder class:

public IEnumerable<folder> items { get; set; }

Now you can recursively search childs for each root folder:

public static IEnumerable<folder> BuildTree(folder current, folder[] allItems)
{
    var childs = allItems.Where(c => c.pid == current.id).ToArray();
    foreach (var child in childs)
        child.items = BuildTree(child, allItems);
    current.items = childs;
    return childs;
}

Usage:

var input = new[] // dt.AsEnumerable() in your case
{
    new folder {id = "F182",  caption = "GLOBAL REPORTS",      pid = null   },
    new folder {id = "F184",  caption = "software",            pid = null   },
    new folder {id = "F1227", caption = "LYB P&L Reports",     pid = "F184" },
    new folder {id = "F1245", caption = "LYB Training",        pid = "F184" },
    new folder {id = "F1239", caption = "test3",               pid = "F182" },
    new folder {id = "F1249", caption = "Paavan_Test_Reports", pid = "F184" },
};

var roots = input.Where(i => i.pid == null);
foreach (var root in roots)
    BuildTree(root, input);

var json = JsonConvert.SerializeObject(roots, Formatting.Indented);

Also, if you want to hide empty items you can define method ShouldSerialize in folder class:

public bool ShouldSerializeitems()
{
    return items.Any();
}

Demo is here

Answer 2

I think what you want is to have a list of folders inside the folder, like the following:

class folder
{
    string id{get;set;}
    string pid{get;set;}
    string caption{get;set;}
    List<folder> items {get;set;}
}

When you use JSONConvert in this case, you can run into having circular dependencies, if a parent exists in the child's collection. You can use a SerializerSettings object to ignore these cases, and for children in the items list, you can set their collection to null, so it won't serialized.

However, the desired output you specify also lists an array of folders. So in the end, you'll need to serialize a collection(List or array for example) of the folder class. Something like.

With regards to how you would load in the data, I imagine you've already loaded the folders with id, pid and caption, but the items property is empty.

In order to establish the parent/child relation, you could so something like

var folders = dt.AsEnumerable().ToList();

foreach(var folder in folders) {
    folder.items = folders.Where(f => f.pid.Equals(folder.id)).ToList();
}

var parentFolders = folders.Where(f => f.pid is null);

var json = JsonConvert.SerializeObject(parentFolders, new SerializerSettings () {
    ReferenceLoopHandling = ReferenceLoopHandling.Ignore
    });