Querying json key name in SQL Server

Question

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?

Jonasr · Accepted Answer · 2017-11-24 23:57:00Z

19

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

edited Nov 24, 2017 at 23:57

answered Nov 24, 2017 at 23:46

Jonasr

2062 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

El Ronnoco Over a year ago

I like how this guy comes out of nowhere and smashes this answer out!

John Cappelletti · Accepted Answer · 2017-05-29 02:13:30Z

0

Option Using a Table

Declare @YourTable table (ID int,JSON_String varchar(max))
Insert Into @YourTable values
 (1,'{"setting1":"A","setting2":"B","setting3":"C"}')

Select A.ID
      ,C.*
 From  @YourTable  A
 Cross Apply (values (try_convert(xml,replace(replace(replace(replace(replace(JSON_String,'"',''),'{','<row '),'}','"/>'),':','="'),',','" '))) ) B (XMLData)
 Cross Apply (
                Select Name   = attr.value('local-name(.)','varchar(100)')
                      ,Value  = attr.value('.','varchar(max)') 
                 From  B.XMLData.nodes('/row') as C1(r)
                 Cross Apply C1.r.nodes('./@*') as C2(attr)
             ) C

Returns

ID  Name        Value
1   setting1    A
1   setting2    B
1   setting3    C

Option Using a String Variable

Declare @String varchar(max) = '{"setting1":"A","setting2":"B","setting3":"C"}'

Select C.* 
 From  (values (try_convert(xml,replace(replace(replace(replace(replace(@String,'"',''),'{','<row '),'}','"/>'),':','="'),',','" '))) ) A (XMLData)
 Cross Apply (
                Select Name   = attr.value('local-name(.)','varchar(100)')
                      ,Value  = attr.value('.','varchar(max)') 
                 From  A.XMLData.nodes('/row') as C1(r)
                 Cross Apply C1.r.nodes('./@*') as C2(attr)
             ) C

Returns

Name       Value
setting1    A
setting2    B
setting3    C

edited May 29, 2017 at 2:13

answered May 29, 2017 at 2:05

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

4 Comments

Eilert Hjelmeseth Over a year ago

Very clever workaround! Unfortunately, the json I'm working with is slightly more complicated than my example I gave, but the root of my question is looking to see if there is a way to get the key name from the json similarly to how you can get it with xml. Sadly it sounds like that's not possible? Here is some json closer to what I'm working with :{"setting1":{"global":"A","type":"1"},"setting2":{"global":"B","type":"1"},"setting3":{"global":"C","type":"1"}}

Eilert Hjelmeseth Over a year ago

I was able to adapt your approach to my needs, however my real question was if you're able to use something like local-name() to get the key name in JSON. If you're able to definitively state that there is no way to do that and update your answer to say such then I'll accept this as an answer as it provides a pretty good workaround.

John Cappelletti Over a year ago

@EilertHjelmeseth Actually, I spent a fair amount of time experimenting with 2016's JSON functionality (mostly because this was an intesting question), but I've yet to find/create a concise solution. I may have another solution, but it requires my EXTRACT UDF. It is modification to a parse udf which accept two non-like delimiters.

John Cappelletti Over a year ago

@EilertHjelmeseth I added a second answer

John Cappelletti · Accepted Answer · 2017-05-30 13:59:38Z

If you are open to a TVF.

The following requires my Extract UDF. This function was created because I was tired of extracting string (patindex,charindex,left,right, etc). It is a modified tally parse which accepts two non-like delimiters.

Example

Declare @YourTable table (ID int,JSON_String varchar(max))
Insert Into @YourTable values
 (1,'{"setting1":{"global":"A","type":"1"},"setting2":{"global":"B","type":"1"},"setting3":{"global":"C","type":"1"}} ')


Select A.ID
      ,B.Setting
      ,C.*
 From  @YourTable  A
 Cross Apply (
                Select Setting = replace(replace(B1.RetVal,'"',''),'{','')
                      ,B2.RetVal
                 From  [dbo].[udf-Str-Extract](A.JSON_String,',',':{') B1
                 Join  [dbo].[udf-Str-Extract](A.JSON_String,':{','}') B2
                   on  B1.RetSeq=B2.RetSeq
             ) B
 Cross Apply (
                Select Name  = C1.RetVal
                      ,Value = C2.RetVal
                 From  [dbo].[udf-Str-Extract](','+B.RetVal,',"','":') C1
                 Join  [dbo].[udf-Str-Extract](B.RetVal+',',':"','",') C2
                   on  C1.RetSeq=C2.RetSeq
             ) C

Returns

ID  Setting     Name    Value
1   setting1    global  A
1   setting1    type    1
1   setting2    global  B
1   setting2    type    1
1   setting3    global  C
1   setting3    type    1

The UDF if Interested

CREATE FUNCTION [dbo].[udf-Str-Extract] (@String varchar(max),@Delimiter1 varchar(100),@Delimiter2 varchar(100))
Returns Table 
As
Return (  

with   cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
       cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 N1,cte1 N2,cte1 N3,cte1 N4,cte1 N5,cte1 N6) A ),
       cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter1) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter1)) = @Delimiter1),
       cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter1,@String,s.N),0)-S.N,8000) From cte3 S)

Select RetSeq = Row_Number() over (Order By N)
      ,RetPos = N
      ,RetVal = left(RetVal,charindex(@Delimiter2,RetVal)-1) 
 From  (
        Select *,RetVal = Substring(@String, N, L) 
         From  cte4
       ) A
 Where charindex(@Delimiter2,RetVal)>1

)
/*
Max Length of String 1MM characters

Declare @String varchar(max) = 'Dear [[FirstName]] [[LastName]], ...'
Select * From [dbo].[udf-Str-Extract] (@String,'[[',']]')
*/

Collectives™ on Stack Overflow

Querying json key name in SQL Server

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related