snippetsqlMinor
How can get this value with json sql server?
Viewed 0 times
thiscanserverwithsqlvaluegethowjson
Problem
How can I get question_id field in this json file? I tried, but it returns null.
Result
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
You can use
If you have an array of objects on the root you would first need to shred on
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.