How can I query a table with key/value pairs into a JSON object using FOR JSON PATH?

Question

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"}]

Answer 1

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"}] |
+---------+--------------------------------------+

Answer 2

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')

DECLARE @resultJson VARCHAR(MAX) = '{}'

SELECT
    @resultJson = JSON_MODIFY(@resultJson, '$.' + Name, Val)
FROM @Att

SELECT @resultJson AS [@resultJson]

Return

@resultJson
{"height":"100px","width":"200px"}

Answer 3

I know your original question asked for a solution using FOR XML, but you also said you want a solution which:

• avoids dynamic queries
• allows for an undefined set of key names

Have you considered the "stuff method" for accomplishing this, which uses the STUFF function along with FOR XML?

I've been using this for a while now successfully, though in some use cases you may run into performance concerns.

It doesn't require pivoting, doesn't require use of dynamic queries, and allows for varying numbers of key/value pairs per row.

Here is how it's done using your example above...

WITH 
    ASSETS AS 
    (
        SELECT 1 ASSETID, 'height' NAME, '100px' VALUE UNION
        SELECT 1 ASSETID, 'width' NAME, '200px' VALUE UNION
        SELECT 2 ASSETID, 'height' NAME, '900px' VALUE UNION
        SELECT 2 ASSETID, 'width' NAME, '1800px' VALUE UNION
        SELECT 2 ASSETID, 'randomKey' NAME, 'ABC123' VALUE
    )

SELECT 
    A.ASSETID, 
    '[' + STUFF
    (
        (
            SELECT 
                N',{"name":"' + B.NAME + '","value":"' + B.VALUE + '"}'
            FROM 
                ASSETS B
            WHERE 
                A.ASSETID = B.ASSETID               
            FOR
                XML PATH(N''), 
                TYPE
        ).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'' --the last param is blank... it replaces the leading comma with ''
    ) + ']' VALUE

FROM 
    ASSETS A
GROUP BY 
    ASSETID

Output:

ASSETID VALUE
1   [{"name":"height","value":"100px"},{"name":"width","value":"200px"}]
2   [{"name":"height","value":"900px"},{"name":"width","value":"1800px"},{"name":"randomKey","value":"ABC123"}]

I KNOW it's not a perfect solution to what you originally asked (because it doesn't use FOR JSON as your originally requested, but it is one workaround that meets all of your other criteria.

Answer 4

Also you can use some "fake" json composing, like this:

select [AssetID],
[Attributes] = (
    select '{'+STRING_AGG ('"'+[Name]+'":"'+[Val]+'"', ',')+'}'
    from @Att
    where [AssetID] = [Asset].[AssetID]) from @Asset as [Asset]

See an example "Create a nested JSON structure" at https://learn.microsoft.com/en-us/sql/relational-databases/json/solve-common-issues-with-json-in-sql-server

Answer 5

DECLARE @Att TABLE (AssetID INT, Attributes NVARCHAR(100))
INSERT @Att VALUES (1, '{"height":"100px","width":"200px"}')

SELECT AssetID, x.[Key], x.[Value]
FROM @Att
CROSS APPLY OPENJSON(Attributes, '$') AS x;

Return

AssetID  Key    Value
----------------------
1        height 100px
1        width  200px

See Advanced JSON Techniques

Answer 6

Here's a way to do it using a CTE.

DECLARE @Att TABLE (AssetID INT, Name NVARCHAR(100), Val NVARCHAR(100))
INSERT @Att VALUES 
    (1, 'height', '100px'), (1, 'width', '200px'), -- Asset 1
    (2, 'height', '50px'), (2, 'color','blue'); -- Asset 2

WITH attr AS (
    SELECT AssetId, 
        Attributes = JSON_MODIFY('{}', '$.' + Name, Val)
    FROM @Att)
SELECT AssetId, 
        Attributes = REPLACE(STRING_AGG(attr.Attributes, ','), '},{', ',')
    FROM attr
    GROUP BY AssetId;

Result:

AssetId	Attributes
1	{"height":"100px","width":"200px"}
2	{"height":"50px","color":"blue"}

How It Works

JSON_MODIFY: In the first query (the CTE), JSON_MODIFY creates name/values -- with proper JSON quoting and escaping.

SELECT AssetId, Attributes = JSON_MODIFY('{}', '$.' + Name, Val) FROM @Att

AssetId	Attributes
1	{"height":"100px"}
1	{"width":"200px"}
2	{"height":"50px"}
2	{"color":"blue"}

STRING_AGG: In the main query, STRING_AGG with GROUP BY combines the attributes.

WITH attr AS (
    SELECT AssetId, 
        Attributes = JSON_MODIFY('{}', '$.' + Name, Val)
    FROM @Att)
SELECT AssetId, 
        Attributes = STRING_AGG(attr.Attributes, ',')
    FROM attr
    GROUP BY AssetId;

AssetId	Attributes
1	{"height":"100px"},{"width":"200px"}
2	{"height":"50px"},{"color":"blue"}

REPLACE: To get a single object from the result above, a simple REPLACE to get rid of the curly brackets:

REPLACE(STRING_AGG(attr.Attributes, ','), '},{', ',')

AssetId	Attributes
1	{"height":"100px","width":"200px"}
2	{"height":"50px","color":"blue"}