Background:
I have a JSON nvarchar(max)
column named 'questions' that looks like this real example from a single row...
{"211":0,"212":0,"213":0,"214":0,"215":0,"216":0,"217":0,"218":0,"219":0,"220":"1","221":"1","222":"1","223":"1","224":"1","225":"1","226":"1","227":"1","228":"1","229":"1","230":"1","231":"1","232":"1"}
I'm currently generating this example JSON snippet for an example 'call'...
[
{
"call": {
"id": 200643,
"yes_answers": [
{
"question_id": "220"
},
{
"question_id": "221"
},
{
"question_id": "222"
},
{
"question_id": "223"
},
{
"question_id": "224"
},
{
"question_id": "225"
},
{
"question_id": "226"
},
{
"question_id": "227"
},
{
"question_id": "228"
},
{
"question_id": "229"
},
{
"question_id": "230"
},
{
"question_id": "231"
},
{
"question_id": "232"
}
]
}
}
]
.. using this query...
select c.call_id as [call.id],
(
select x.[key]
from [call].[triage_questions] tq
cross apply openjson(questions, '$') as x
where value = 1 and tq.call_id = c.call_id
for json path
) as [call.yes_answers]
from [call].[dimension] c
where call_id = 200643
for json path
My Question:
I don't like the way the "yes_answers" array is being formatted. I'd like something more like this:
[
{
"call": {
"id": 200643,
"yes_answers": [
220,
221,
222,
223,
224,
225,
226,
227,
228,
229,
230,
231,
232
]
}
}
]
Is this valid? or should I be rolling this up a different way? or just leave it?
I'll be using SQL to reference each question_id in the "yes_answers" array to a reference table where I'm looking for a true/false flag associated with that question. The JSON will not be leaving SQL at this time, if that matters.
EDIT:
Thanks to @Tomato32, I've found this other question that helped me get close enough, I think.
My query now looks like this...
select c.call_id as [call.id],
json_query(replace(replace((
select x.[key] as question_id
from [call].[triage_questions] tq
cross apply openjson(questions, '$') as x
where value = 1 and tq.call_id = c.call_id
order by x.[key]
for json path
), N'{"question_id":', N''), '"}', '"')) as [call.yes_answers]
from [call].[dimension] c
where call_id = 200643
for json path
And my resulting JSON looks like this...
[
{
"call": {
"id": 200643,
"yes_answers": [
"220",
"221",
"222",
"223",
"224",
"225",
"226",
"227",
"228",
"229",
"230",
"231",
"232"
]
}
}
]
I can't get rid of the quotes around the int values in the "yes_answers" array, but I don't think it's going to matter, and I'm not going to waste any time on it :) Thank you all!!!
I only updated your replace statements to get rid of the quotation marks in "[number]", as below:-
select c.call_id as [call.id],
json_query(replace(replace((
select x.[key] as question_id
from [call].[triage_questions] tq
cross apply openjson(questions, '$') as x
where value = 1 and tq.call_id = c.call_id
order by x.[key]
for json path
), N'{"question_id":"', N''), '"}', '')) as [call.yes_answers]
from [call].[dimension] c
where call_id = 200643
for json path
In my testing, the output is as below:-
[{"call":{"id":200643,"yes_answers":[220,221,222,223,224,225,226,227,228,229,230,231,232]}}]