HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How can get this value with json sql server?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
thiscanserverwithsqlvaluegethowjson

Problem

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

Solution

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;

Code Snippets

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;
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
   }
]'

Context

StackExchange Database Administrators Q#187228, answer score: 7

Revisions (0)

No revisions yet.