How can get this value with json sql server?

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

enter image description here

Answer 1

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;