Querying json key name in SQL Server

user_3411515 0
Viewed 15496

Given json like this...

{"setting1":"A","setting2":"B","setting3":"C"}

I would like to see results like...

+----------+-------+
|   name   | value |
+----------+-------+
| setting1 | A     |
| setting2 | B     |
| setting3 | C     |
+----------+-------+

My struggle is I'm trying to find out how to extract the key's name (i.e., "setting1", "setting2", "setting3", etc.)

I could do something like the following query, but I don't know how many settings there will be and what their names will be, so I'd like something more dynamic.

SELECT
    B.name,
    B.value
FROM OPENJSON(@json) WITH
    (
        setting1 varchar(50) '$.setting1',
        setting2 varchar(50) '$.setting2',
        setting3 varchar(50) '$.setting3'
    ) A
CROSS APPLY
    (
        VALUES
            ('setting1', A.setting1),
            ('setting2', A.setting2),
            ('setting3', A.setting3)
    ) B (name, value)

With XML, I could do something simple like this:

DECLARE @xml XML = '<settings><setting1>A</setting1><setting2>B</setting2><setting3>C</setting3></settings>'
SELECT
    A.setting.value('local-name(.)', 'VARCHAR(50)') name,
    A.setting.value('.', 'VARCHAR(50)') value
FROM @xml.nodes('settings/*') A (setting)

Any way to do something similar with SQL Server's json functionality?

sql-server json
3 Answers

Aaron Bertrand has written about json key value in Advanced JSON Techniques

SELECT x.[Key], x.[Value] 
FROM OPENJSON(@Json, '$') AS x;

Return

Key         Value
------------------
setting1    A
setting2    B
setting3    C
user_47536460
Related