Create JSON elements based on table content

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 achie

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

StackExchange Database Administrators Q#331386, answer score: 3