I have the following SQL data that I am trying to output as a structured JSON string as follows:
Table data
TableId ContainerId MaterialId SizeId
848 1 1 1
849 1 1 2
850 1 2 1
851 1 2 2
852 1 3 1
853 1 4 1
854 2 2 1
855 2 2 2
856 2 2 3
JSON output
{
"container": [
{
"id": 1,
"material": [
{
"id": 1,
"size": [
{
"id": 1
},
{
"id": 2
}
]
},
{
"id": 2,
"size": [
{
"id": 1
},
{
"id": 2
}
]
},
{
"id": 3,
"size": [
{
"id": 1
}
]
},
{
"id": 4,
"size": [
{
"id": 1
}
]
}
]
},
{
"id": 2,
"material": [
{
"id": 2,
"size": [
{
"id": 1
},
{
"id": 2
},
{
"id": 3
}
]
}
]
}
]
}
I have tried several ways of outputting it but I am struggling to stop duplicated Container and Material Id records. Is anyone able to demonstrate the best working practices for extracting JSON from a table such as this please?
Well, it isn't pretty but this appears to work:
WITH
container As (SELECT distinct containerid As id FROM jsonArray1 As container)
, material As (SELECT distinct materialid As id, containerid As cid FROM jsonArray1 As material)
, size As (SELECT sizeid As id, materialid As tid, containerid As cid FROM jsonArray1 As size)
SELECT container.id id, material.id id, size.id id
FROM container
JOIN material ON material.cid = container.id
JOIN size ON size.tid = material.id AND size.cid = material.cid
FOR JSON AUTO, ROOT
AUTO will structure JSON for you, but only by following the structure of the data tables used in the query. Since the data starts out "flat" in a single table, AUTO won't create any structure. So the trick I applied here was to use WITH CTE's to restructure this flat data into three virtual tables whose relationships had the necessary structure.
Everything here is super-sensitive in a way that normal relational SQL would not be. For instance, just changing the order of the JOINs will restructure the JSON hierarchy even though that would have no effect on a normal SQL query.
I also had to play around with the table and column aliases (a lot) to get it to put the right names on everything in the JSON.