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

6

7

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"}]
Lightyear answered 28/1, 2017 at 15:38 Comment(2)
Is it always Height and Width ? or it can be anything ?Michaelamichaele
It could be anything. There is no limit on the name of the keyLightyear
G
3

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"}] |
+---------+--------------------------------------+
Gerrit answered 28/1, 2017 at 15:57 Comment(6)
Thanks, it looks similar to an example I saw for XML. I was hoping to do this without executing dynamic SQL, but perhaps that's the only way.Lightyear
@ChadGilbert - Lets wait for some gurus. May be any native JSON methods could be there to get it doneMichaelamichaele
The converse query is easy: SELECT [key], [value] FROM OPENJSON('{"height":"100px","width":"200px"}', '$') so I'm hoping there's an easy and elegant way built-inLightyear
@ChadGilbert - I was checking in google for any possibility, I don't think it is possible to get the data inside column as Key in JSON.Michaelamichaele
I'll give it a few days to see if there are any other solutions, otherwise I'll mark yours as accepted. Thanks againLightyear
@ChadGilbert - Lets wait :)Michaelamichaele
P
1
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"}
Paunch answered 11/7, 2023 at 11:49 Comment(1)
Clever trick with JSON_MODIFY, I used it in my answer.Dressy
G
0

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.

Gracious answered 28/3, 2017 at 10:46 Comment(1)
Thanks, but unescaped string concatenation is a deal breakerLightyear
J
0

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

Jorum answered 22/5, 2017 at 14:29 Comment(2)
You can improve the quality of your Answer by phrasing it as an answer rather than a question, i.e. don't start with What about.... It would also benefit from a description of how your suggestion works.Bruno
Thank you for advice @BrunoJorum
S
0
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

Somewise answered 25/11, 2017 at 0:8 Comment(0)
D
0

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"}
Dressy answered 2/4 at 23:48 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.