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.