How to generate hierarchical JSON from SQL Server data table

1.2k views Asked by At

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

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 sql query that generate JSON structure from my sqlserver table to use it in primeng treetable component. it requires json structure like this:

{
"data":
[
    {
        "data":{
            "name":"Documents",
            "size":"2k",
            "type":"Folder"
        },
        "children":[
            {
                "data":{
                    "name":"Work",
                    "size":"5k",
                    "type":"Folder"
                },
                "children":[
                    {
                        "data":{
                            "name":"Expenses.doc",
                            "size":"30kb",
                            "type":"Document"
                        }
                    },
                    {
                        "data":{
                            "name":"Resume.doc",
                            "size":"25kb",
                            "type":"Resume"
                        }
                    }
                ]
            },
            {
                "data":{
                    "name":"Home",
                    "size":"20kb",
                    "type":"Folder"
                },
                "children":[
                    {
                        "data":{
                            "name":"Invoices",
                            "size":"20kb",
                            "type":"Text"
                        }
                    }
                ]
            }
        ]
    },
    {
        "data":{
            "name":"Pictures",
            "size":"150kb",
            "type":"Folder"
        },
        "children":[
            {
                "data":{
                    "name":"barcelona.jpg",
                    "size":"90kb",
                    "type":"Picture"
                }
            },
            {
                "data":{
                    "name":"primeui.png",
                    "size":"30kb",
                    "type":"Picture"
                }
            },
            {
                "data":{
                    "name":"optimus.jpg",
                    "size":"30kb",
                    "type":"Picture"
                }
            }
        ]
    }
]}

link of primeng tree table component and related documentation is here. also i'm using sql server 2017.

thanks.

0

There are 0 answers