What is the best practise for outputting JSON Type arrays from Table values without repetition?-CodePudding

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?

CodePudding user response：

You can use the following query

SELECT CONCAT('{"container": [',string_agg(json,','),']}') as json
FROM 


  (SELECT CONCAT('{"id:"',CAST(ContainerId as nvarchar(100)),
       ',"material":[{',string_agg(json,','),'}]}') as json,
       dense_rank() over(partition by ContainerId order by ContainerId) rnk
  FROM
     (SELECT ContainerId ,MaterialId,CONCAT('"id":',CAST(MaterialId as nvarchar(100))
           ,',"size":[',string_agg('{"id":'   CAST(SizeId as nvarchar(100))   '}',','),']') as json
         
     FROM tb  
     GROUP BY ContainerId,MaterialId) T
  GROUP BY ContainerId) T

GROUP BY rnk

demo in db<>fiddle

CodePudding user response：

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

sqlfiddle example

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.