I have sql server hierarchical data table with this sample structure and data:

Question

My table structure is:

Id      name     size     type      parrent_Id

1       AAAA     2k        t1       null
2       BB       2k        t2       1
3       CC       1k        t3       1
4       DDDD     2k        t4       null
5       EE       2k        t5       4
6       FF       1k        t6       5 

I need a SQL query that generates JSON structure from table to use it in primeng tree table component. It requires JSON structure like this. iam using asp.net core web api with sql server:

      {
            "data":
            [
                {
                    "data":{
                        "name":"Documents",
                        "size":"2k",
                        "type":"Folder"
                    },
                    "children":[
                        {
                            "data":{
                                "name":"Work",
                                "size":"5k",
                                "type":"Folder"
                         },
                    ]
                }
             ]

Answer 1

Suppose you're using EF Core and your Model looks like :

public class XModel {
    public int Id {get;set;}
    public string Name {get;set;}
    public string Size {get;set;}
    public string Type {get;set;}

    public int? ParentId {get;set;}
    public XModel Parent {get;set;}

    public IList<XModel> Children {get;set;}
}

Since you expects a model with a data & children field. Let's create DTO models for them:

public class Data {
    public int Id {get;set;}
    public string Name {get;set;}
    public string Size {get;set;}
    public string Type {get;set;}

    [JsonIgnore]
    public int? ParentId {get;set;}
}

public class Dto {
    public Data Data {get;set;}
    public IList<Dto> Children{get;set;}
}

Let's built an extension method that builds the tree

public static class TreeLikeExtensions
{
    public static IList<Dto> BuildTrees(this IQueryable<XModel> models)
    {
        var dtos = models.Select(m =>new Dto{
            Data = new Data { Id = m.Id, Name = m.Name, Size =m.Size, Type = m.Type, ParentId = m.ParentId, },
            Children = null,
        }).ToList();
        return BuildTrees(null, dtos);
    }

    // private helper function that builds tree recursively
    private static IList<Dto> BuildTrees(int? pid, IList<Dto> candicates)
    {
        var children = candicates.Where(c => c.Data.ParentId == pid).ToList();
        if (children==null || children.Count() == 0){
            return null; 
        }
        foreach (var i in children){
            i.Children= BuildTrees(i.Data.Id, candicates);
        }
        return children;
    }
}

To get the trees, just invoke BuildTrees():

var result = _context.XModel.BuildTrees();

To ignore the null children property when serializing, just add a settings as below:

// var settings= new JsonSerializerSettings{
//     NullValueHandling = NullValueHandling.Ignore,
//     ReferenceLoopHandling = ReferenceLoopHandling.Ignore,
// }

Or configure MVC Serivces in Startup.cs:

services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2)
    .AddJsonOptions(o =>{
        o.SerializerSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore;
        o.SerializerSettings.NullValueHandling = NullValueHandling.Ignore;
    });

---

A Working Demo