You need a statement like the following. You should parse the input JSON with OPENJSON()
and explicit schema (columns definitions):
Table:
CREATE TABLE Data (JsonData varchar(1000))
INSERT INTO Data (JsonData)
VALUES ('{"A":100,
"B":[{"Title":"Age","Match":false},{"Title":"Height","Match":false},{"Title":"Country","Match":false},{"Title":"Gender","Match":false},{"Title":"Language","Match":true}],
"C":78}')
Statement:
SELECT j1.A, j1.C, j2.*
FROM Data d
CROSS APPLY OPENJSON (d.JsonData) WITH (
A int '$.A',
B nvarchar(max) '$.B' AS JSON,
C int '$.C'
) j1
CROSS APPLY OPENJSON (j1.B) WITH (
Title varchar(500) '$.Title',
Match bit '$.Match'
) j2
Result:
A C Title Match
100 78 Age False
100 78 Height False
100 78 Country False
100 78 Gender False
100 78 Language True
Note, that the reason for the NULL
results is the fact, that B
key in the input JSON is a JSON array, so you need to use JSON_QUERY()
to get the whole JSON array and additional CROSS APPLY
to parse this array. JSON_VALUE()
extracts a scalar value from a JSON
string and if the path
points to not a scalar value, the result is NULL
in lax
mode and an error in strict
mode.
SELECT
JSON_VALUE(d.JsonData, '$.A') AS A,
JSON_QUERY(d.JsonData, '$.B') AS B,
JSON_VALUE(d.JsonData, '$.C') AS C,
j.*
FROM Data d
CROSS APPLY OPENJSON(d.JsonData, '$.B') WITH (
Title varchar(500) '$.Title',
Match bit '$.Match'
) j