I have an Asset table and an Attributes table, where the attributes are simple key/value pairs.
DECLARE @Asset TABLE(AssetID INT)
INSERT @Asset VALUES (1)
DECLARE @Att TABLE (AssetID INT, Name NVARCHAR(100), Val NVARCHAR(100))
INSERT @Att VALUES (1, 'height', '100px'), (1, 'width', '200px')
I would like to write a query that groups by Asset and contains a column with the JSON representation of all attributes. For example:
AssetID Attributes
------------ -----------------------------------------------
1 {"height":"100px","width":"200px"}
How can I write the query so that the attribute name value becomes the key in the resulting JSON object? When I use FOR JSON PATH, the keys are the column names:
SELECT
AssetID,
(
SELECT Name, Val
FROM @Att att
WHERE att.AssetID = asset.AssetID
FOR JSON PATH
) Attributes
FROM @Asset asset
which returns...
AssetID Attributes
------------ -----------------------------------------------
1 [{"Name":"height","Val":"100px"},{"Name":"width","Val":"200px"}]