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.