OPENJSON expanded with other columns

Question

I have a table that contains data like

id     deltas
23     [{"prop1": "value1","prop2": "value2},{"prop1": "value3","prop2": "value4}

The idea here is that the deltas contain an array of objects. The schema of the objects is always prop1 and prop2 lets say. Meaning the objects will only contain those 2 properties.

What I am after is to write TSQL query that gives me something like:

id   property   value
23   prop1      value1
23   prop2      value2
23   prop1      value3
23   prop2      value4

I can use openjson already because I set the compatibility level to 130. I tried a simple openjson, but what it gives me is a key value with the first object as 0 key and value as the json. I want to parse that JSON of the value.

Answer 1

You can use OPENJSON to extract the values from JSON and then UNPIVOT them to reorganize the values in your desired format:

--temp table variable that holds your json
declare @source table (id int, props nvarchar(max))
--temp table variable that holds values extracted from your json
declare @tmp table (id int, prop1 nvarchar(max), prop2 nvarchar(max))

--populate temp table with the data you posted
insert into @source
select 23,'[{"prop1": "value1","prop2": "value2"},{"prop1": "value3","prop2": "value4"}]'

--use OPENJSON to extract data from "props" column holding your json
insert into @tmp
select id, prop1,prop2 from  @source  cross apply OPENJSON(props) 
with
(
    prop1 nvarchar(100),
    prop2 nvarchar(100)
)

--unpivot the result to get desired result
select id, u.property, u.value
from  @tmp t
unpivot(
    [value] for [property] in (prop1,prop2)
) u;

Here is the output of this TSQL fragment:

If you don't need the intermediate results you can avoid using the temp table and process your data in a single step:

--temp table variable that holds your json
declare @source table (id int, props nvarchar(max))

--populate temp table with the data you posted
insert into @source
select 23,'[{"prop1": "value1","prop2": "value2"},{"prop1": "value3","prop2": "value4"}]'

--use openjson to fetch data from JSON and then unpivot the result to get desired format
select id, u.property, u.value
from  (select id, prop1, prop2 from  @source  cross apply OPENJSON(props) 
with
(
    prop1 nvarchar(100),
    prop2 nvarchar(100)
)
) t
unpivot(
    [value] for [property] in (t.prop1,t.prop2)
) u;

P.S.

The JSON you posted:

[{"prop1": "value1","prop2": "value2},{"prop1": "value3","prop2": "value4}

is not valid because the final square bracket is missing together with a couple of quotes after value2 and value4. You can check this using ISJSON() which will return 0:

select isjson(' [{"prop1": "value1","prop2": "value2},{"prop1": "value3","prop2": "value4}')

So I assumed that the input is actually:

[{"prop1": "value1","prop2": "value2"},{"prop1": "value3","prop2": "value4"}]