Parse JSON array inside an object in T-SQL

Question

I have a json array like this:

{
    "TagsDictionary": [
        { 
            "key" : "property1",
            "value" : "property1Value"
        },
        {
            "key" : "property2",
            "value" : "property2Value"
        }
    ]
}

I need to query it in T-SQL. I want this result:

property1      | property2
property1Value | property2Value

I read docs. But I can't achieve result with my structure. Unfortunately the JSON is stored in the database and I can't change it, because other modules depends on it.

I'm using SQL Server 2016.

Does this JSON contain only two items in the "TagsDictionary" JSON array? — Zhorov
– Zhorov, Commented Mar 29, 2020 at 17:34

John Cappelletti · Accepted Answer · 2020-03-29 16:43:46Z

Not 100% sure this is what you are looking for

Example

Declare @YourTable table (ID int,JSON varchar(max))
Insert Into @YourTable values
(1,'{"TagsDictionary": [{"key" : "property1","value" : "property1Value"},{"key" : "property2","value" : "property2Value"}]}')


;with cte as (
Select A.ID
      ,RN  = row_number() over (partition by id,Indx order by B.[Key])
      ,CN  = Indx+1
      ,B.[value] 
From  @YourTable A
Cross Apply (
                Select Indx = B.[key]
                      ,C.[key]
                      ,C.[value] 
                 From  OpenJSON(A.JSON) A
                 Cross Apply OpenJSON(A.Value) B
                 Cross Apply OpenJSON(B.Value) C
            ) B
) 
Select *
 From  cte
 Pivot ( max(value) for CN in ([1],[2]) ) pvt

Returns

ID  RN  1               2
1   1   property1       property2
1   2   property1Value  property2Value

Collectives™ on Stack Overflow

Parse JSON array inside an object in T-SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related