Frequently in Javascript you'll have something like
[ 7,2, [6,7], 2,10 ]
How would you query that structure with OPENJSON
I would like this,
0, 7
1, 2
2, 6,
2, 7,
3, 2
4, 10
I'm having a hard time conditionally unwrapping that JSON array.
Sample Data
declare @ex nvarchar(max) = '[ 7,2, [6,7], 2,10 ]';
My attempt
SELECT *
FROM OPENJSON(@ex, '$') AS j1
This gets you to,
key value type
0 7 2
1 2 2
2 [6,7] 4
3 2 2
4 10 2
If I try to CROSS APPLY OPENJSON(j1."value", '$') AS j2;
, I get an error about the 7, the first non-array being an invalid array,
Msg 13609 Level 16 State 4 Line 4
JSON text is not properly formatted. Unexpected character '7' is found at position 0.
How do I use CROSS APPLY OPENJSON
to conditionally unwrap the rows that are arrays (type=4) while leaving alone non-arrays (in the above like type=2
)? I don't want that [6,7]
in there. I want two rows with key=2
that have values 6
, and 7
respectively.