Create JSON elements based on table content

Question

With the following database schema, I am trying to retrieve a JSON of the items combined with properties and an indicator if they are connected.

I started with:

DECLARE @Item TABLE
(
   ItemId INT,
   Name VARCHAR(25)
);

INSERT INTO @Item
(
   ItemId,
   Name
)
VALUES
(
   1,
   'Item 1'
),
(
   2,
   'Item 2'
);

DECLARE @Property TABLE
(
   PropertyId INT,
   Name VARCHAR(10)
);

INSERT INTO @Property
(
   PropertyId,
   Name
)
VALUES
(
   1,
   'Property 1'
),
(
   2,
   'Property 2'
),
(
   3,
   'Property 3'
);

DECLARE @ItemProperty TABLE
(
   ItemId INT,
   PropertyId INT
);

INSERT INTO @ItemProperty
(
   ItemId,
   PropertyId
)
VALUES
(
   1,
   1
),
(
   1,
   2
),
(
   2,
   2
),
(
   2,
   3
);

SELECT *
FROM   @Item AS i
       OUTER APPLY (
                      SELECT
                           p.Name,
                           CASE WHEN ip.PropertyId IS NOT NULL THEN
                                   CAST(1 AS BIT)
                                ELSE CAST(0 AS BIT)
                           END AS ItemExists
                      FROM @Property AS p
                           LEFT JOIN @ItemProperty AS ip ON ip.PropertyId = p.PropertyId
                                                            AND ip.ItemId = i.ItemId
                   ) a
WHERE  i.ItemId = 1
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;

This gives me the following result:

{
    "ItemId": 1,
    "Name": "Item 1",
    "a": [
        {
            "Name": "Property 1",
            "ItemExists": true
        },
        {
            "Name": "Property 2",
            "ItemExists": true
        },
        {
            "Name": "Property 3",
            "ItemExists": false
        }
    ]
}

However, I would like to have this as a result

{
    "ItemId": 1,
    "Name": "Item 1",
    "Property 1": true,
    "Property 2": true,
    "Property 3": false
}

And when I am adding a new property to @Property, I would like to have it added automatically.

How can I achieve that?

Answer 1

Unfortunately SQL Server does not at the moment support JSON_OBJECT_AGG, which would have made this type of query significantly easier.

Instead you need to build the JSON manually using STRING_AGG and STRING_ESCAPE

SELECT
  CONCAT(
    '{"ItemId":',
    i.ItemId,
    ',"Name":"',
    STRING_ESCAPE(i.Name, 'json'),
    '"',
    p.Properties,
    '}'
  )
FROM @Item AS i
OUTER APPLY (
    SELECT Properties =
      STRING_AGG(
        ',"' +
        STRING_ESCAPE(p.Name, 'json') +
        '":' +
        IIF(ip.ItemId IS NULL, N'false', N'true'),
        ',"'
      )
    FROM @Property AS p
    LEFT JOIN @ItemProperty AS ip
      ON ip.PropertyId = p.PropertyId
     AND ip.ItemId = i.ItemId
) p
WHERE i.ItemId = 1;

db<>fiddle

Answer 2

One way (I hope is right) , because you have a unknown number of "Property x" , is to use dynamic pivot . See here more about it . https://www.mssqltips.com/sqlservertip/6245/sql-server-dynamic-pivot-query/

I stored the result set into a #tmp table

SELECT i.ItemId, i.Name, a.Name as PropertyName, a.ItemExists
INTO #tmp
FROM   @Item AS i
       OUTER APPLY (
                      SELECT
                           p.Name,
                           CASE WHEN ip.PropertyId IS NOT NULL THEN
                                   CAST(1 AS tinyint)
                                ELSE CAST(0 AS tinyint)
                           END AS ItemExists
                      FROM @Property AS p
                           LEFT JOIN @ItemProperty AS ip ON ip.PropertyId = p.PropertyId
                                                            AND ip.ItemId = i.ItemId
                   ) a
WHERE  i.ItemId = 1;

Then , based on the dynamic pivot :

 DECLARE @columns NVARCHAR (2000),
        @columnsSelect NVARCHAR(2000)=N'',
        @tsql NVARCHAR (2000),
        @unknownValsCol NVARCHAR (100) =N'PropertyName',
        @objNameToPivot NVARCHAR (100) ='#tmp',
        @aggFuncOfPivot NVARCHAR (3)=N'MAX',
        @aggColOfPivot NVARCHAR (100)=N'ItemExists',
        @leadColPivot NVARCHAR (100) =N'itemID,Name',
        @jsonExp NVARCHAR(100) =N'FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER';

   DECLARE @distinctVals TABLE (val NVARCHAR (50));

   SET @columns = N'';
   SET @tsql = CONCAT ('SELECT DISTINCT ', @unknownValsCol,' FROM ',@objNameToPivot)
   INSERT @distinctVals EXEC (@tsql);


   SELECT @columns += CONCAT ('[', Val,']',',')
   FROM @distinctVals;

   SELECT @columnsSelect += CONCAT (',CONVERT(bit,PivotTable.[', Val,']) as [', Val,']')
   FROM @distinctVals;



   SET @columns = LEFT (@columns, LEN (@columns) - 1)
   SET @tsql = CONCAT ( 'SELECT ', @leadColPivot,   @columnsSelect,' FROM ',' ( SELECT ',@leadColPivot,',',
         @aggColOfPivot,',',   @unknownValsCol,   ' FROM ',   @objNameToPivot,   ') as t ',
         ' PIVOT (', @aggFuncOfPivot,   '(', @aggColOfPivot,   ')',' FOR ',   @unknownValsCol,
         '   IN (', @columns,')) as PivotTable ',' ORDER BY ',   @leadColPivot ,' ', @jsonExp)

   EXEC (@tsql);

The output will be:

{
  "itemID":1,
  "Name":"Item 1",
  "Property 1":true,
  "Property 2":true,
  "Property 3":false
}

This is the hardcode version

SELECT itemID,Name,
    CONVERT(bit,PivotTable.[Property 1]) as [Property 1],
    CONVERT(bit,PivotTable.[Property 2]) as [Property 2],
    CONVERT(bit,PivotTable.[Property 3]) as [Property 3]
FROM
(
    SELECT itemID,Name,PropertyName,ItemExists
    FROM #tmp as t
) as src
PIVOT 
(  
  MAX(ItemExists)
  FOR PropertyName IN ([Property 1], [Property 2], [Property 3])  
) AS PivotTable  
ORDER BY itemID, Name
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;

dbfiddle here

without pivot ...

DECLARE @SQL AS VARCHAR(MAX);
DECLARE @WithCols VARCHAR(MAX);
DECLARE @Json VARCHAR(MAX);

SELECT i.ItemId,
        i.Name as ItemName,
        a.Name as PropertyName,
        a.ItemExists
INTO #tmp
FROM   @Item AS i
        OUTER APPLY (
                        SELECT
                            p.Name,
                            CASE WHEN ip.PropertyId IS NOT NULL THEN
                                    CAST(1 AS bit)
                                ELSE CAST(0 AS bit)
                            END AS ItemExists
                        FROM @Property AS p
                            LEFT JOIN @ItemProperty AS ip ON ip.PropertyId = p.PropertyId
                                                            AND ip.ItemId = i.ItemId
                    ) a
WHERE  i.ItemId = 1;


;WITH ColNames AS
(
    SELECT DISTINCT /*TOP(10)*/ PropertyName--,ItemExists
    FROM #tmp
)
SELECT   --TOP(10)
    --Pivoted column names that will be specified in the OPENJSON WITH clause.
    @WithCols = '"itemID" INT ''$.itemID'','  + 
                '"Name" NVARCHAR(100) ''$.Name'','  + 
        STRING_AGG('"' + CAST(PropertyName AS VARCHAR(MAX)) + '" NVARCHAR(100) ''$."' 
                        --+ CAST(PropertyName AS VARCHAR(MAX)) + '"''', ', ') 
                        + CAST(PropertyName AS VARCHAR(MAX)) + '"''', ', ') 
        WITHIN GROUP ( ORDER BY PropertyName )
FROM ColNames;



SELECT @Json = '[' + STRING_AGG(CAST(t.JsonData AS VARCHAR(MAX)), ',') + ']'
FROM
(
    SELECT 
            N'{' + '"itemID":' + CAST(src.ItemId AS VARCHAR(MAX)) + 
                   ',"Name":"' + CAST(src.ItemName AS VARCHAR(MAX)) + 
            
                COALESCE('",' + STRING_AGG('"' + CAST(src.PropertyName AS VARCHAR(MAX)) + '":"'
                                             -- + CAST(CONVERT(bit,src.ItemExists) AS VARCHAR(MAX)), '",'), '') + 
                                              + CONVERT(varchar(MAX),CONVERT(bit,src.ItemExists)), '",'), '') + 
            '"}' 
            AS JsonData
    FROM #tmp as src
    GROUP BY src.ItemId,
          src.ItemName
)as t;


--SELECT @Json
--Parse the JSON data and return a result set.
SET @SQL = '
SELECT *
FROM OPENJSON(''' + @Json + ''')
    WITH
    (
        ' + @WithCols + '
    )
';
--print @sql
EXEC(@SQL);

dbfiddle here