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

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;