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"}]
Am not sure about any native JSON methods to get the column data as Key in JSON. Alias names will be converted to key value in JSON.
So here is my try
You need to pivot the data to get the required key value pair format in JSON
If the key is static then
SELECT
AssetID,
(
SELECT Max(CASE WHEN NAME = 'height' THEN Val END) AS height,
Max(CASE WHEN NAME = 'width' THEN Val END) AS width
FROM @Att att
WHERE att.AssetID = asset.AssetID
FOR JSON path, WITHOUT_ARRAY_WRAPPER
) Attributes
FROM @Asset asset
WITHOUT_ARRAY_WRAPPER is to remove the square brackets that surround the JSON output of the FOR JSON clause by default
Result:
+---------+--------------------------------------+
| AssetID | Attributes |
+---------+--------------------------------------+
| 1 | [{"height":"100px","width":"200px"}] |
+---------+--------------------------------------+
Since the key can be anything we need to use dynamic query to pivot the data
For demo I have changed the table variable to temp table
CREATE TABLE #Asset
(
AssetID INT
)
INSERT #Asset
VALUES (1)
CREATE TABLE #Att
(
AssetID INT,
NAME NVARCHAR(100),
Val NVARCHAR(100)
)
INSERT #Att
VALUES (1,'height','100px'),
(1,'width','200px')
DECLARE @col VARCHAR(8000)= ''
SET @col = (SELECT ',Max(CASE WHEN NAME = ''' + NAME
+ ''' THEN Val END) as ' + Quotename(NAME)
FROM #Att
FOR xml path(''))
SET @col = Stuff(@col, 1, 1, '')
EXEC ('
SELECT
AssetID,
(
SELECT '+@col+'
FROM #Att att
WHERE att.AssetID = asset.AssetID
FOR JSON path, WITHOUT_ARRAY_WRAPPER
) Attributes
FROM #Asset asset')
Result:
+---------+--------------------------------------+
| AssetID | Attributes |
+---------+--------------------------------------+
| 1 | [{"height":"100px","width":"200px"}] |
+---------+--------------------------------------+
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments