Question :
How can I get question_id field in this json file? I tried, but it returns null.
DECLARE @json NVARCHAR(MAX)
SET @json =
N'{
"solution": "xxxxxxxxxxxxxxxxxxxxx",
"options": [
{
"choice_id": 205073,
"choice": "aaaa"
},
{
"choice_id": 205074,
"choice": "bbbb"
},
{
"choice_id": 205075,
"choice": "cccc"
},
{
"choice_id": 205076,
"choice": "dddd"
}
],
"question_id": 12345
}'
SELECT * FROM
OPENJSON ( @json, '$.options')
WITH (
choice_id varchar(8000) '$.choice_id',
question_id int '$.question_id'
)
Result
Answer :
You treat question_id
as if it was a child to options
when it is in fact a sibling to options
.
You can use json_value
in the column list to get the value for question_id
.
select O.choice_id,
json_value(@json, '$.question_id') as question_id
from openjson(@json, '$.options')
with (
choice_id varchar(8000) '$.choice_id'
) as O;
If you have an array of objects on the root you would first need to shred on $
to get one row per object and then use a cross apply
to get the objects.
declare @json nvarchar(max)
set @json = N'
[
{
"solution":"xxxxxxxxxxxxxxxxxxxxx",
"options":[
{
"choice_id":205073,
"choice":"aaaa"
},
{
"choice_id":205074,
"choice":"bbbb"
},
{
"choice_id":205075,
"choice":"cccc"
},
{
"choice_id":205076,
"choice":"dddd"
}
],
"question_id":12345
},
{
"solution":"xxxxxxxxxxxxxxxxxxxxx",
"options":[
{
"choice_id":205073,
"choice":"aaaa"
},
{
"choice_id":205074,
"choice":"bbbb"
},
{
"choice_id":205075,
"choice":"cccc"
},
{
"choice_id":205076,
"choice":"dddd"
}
],
"question_id":22345
}
]'
select json_value(T1.value, '$.question_id'),
T2.choice_id
from openjson(@json, '$') as T1
cross apply openjson(T1.value, '$.options')
with (choice_id varchar(8000))as T2;