Flatten array with OPENJSON: OPENJSON on a value that may not be an array? [ [1] ], vs [1]

Question

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

Find my query here

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.

Answer 1

If you are strictly confident that your nested arrays will never go deeper than N levels, you can completely unwrap the array-of-arrays with N uses of APPLY. If you need to handle for arbitrary nesting levels, you can unwrap the array-of-arrays recursively using something like the following, which will produce output similar to the following

|----|-------|---------|-----|-------|------|
| id | level | path | key | value | type |
|----|-------|---------|-----|-------|------|
| 1 | 1 | /0 | 0 | 7 | 2 |
| 1 | 1 | /1 | 1 | 2 | 2 |
| 1 | 2 | /2/0 | 0 | 6 | 2 |
| 1 | 2 | /2/1 | 1 | 7 | 2 |
| 1 | 1 | /3 | 3 | 2 | 2 |
| 1 | 1 | /4 | 4 | 10 | 2 |
|----|-------|---------|-----|-------|------|

DB Fiddle

declare @ex table ( 
 i int identity primary key,
 r nvarchar(max) 
); 
insert @ex (r) 
values ('[ 7,2, [6,7], 2, 10 ]')
 ,('[ 7,2, [6,7], 2, 10 , [6,[7,8]]]')
;
with j as (
 select 
 x.i as id,
 convert(int,1) as [level],
 convert(nvarchar(max),N'/')+convert(nvarchar(max),a.[key]) as [path],
 a.[key], 
 a.[value],
 a.[type]
 from @ex x 
 cross apply openjson(x.r) a
 union all 
 select 
 j. id,
 j.[level] + 1,
 j.[path] + convert(nvarchar(max),N'/')+convert(nvarchar(max),a.[key]),
 a.[key], 
 a.[value],
 a.[type]
 from j
 cross apply openjson(j.value) a
 where j.type in (4,5)
 and j.level < 50 --maxrecursion
)
select *
from j
where type not in (4,5)
order by id, [path];

Testing against the 2-level deep nested array from the above example produces the following:

|----|-------|---------|-----|-------|------|
| id | level | path | key | value | type |
|----|-------|---------|-----|-------|------|
| 2 | 1 | /0 | 0 | 7 | 2 |
| 2 | 1 | /1 | 1 | 2 | 2 |
| 2 | 2 | /2/0 | 0 | 6 | 2 |
| 2 | 2 | /2/1 | 1 | 7 | 2 |
| 2 | 1 | /3 | 3 | 2 | 2 |
| 2 | 1 | /4 | 4 | 10 | 2 |
| 2 | 2 | /5/0 | 0 | 6 | 2 |
| 2 | 3 | /5/1/0 | 0 | 7 | 2 |
| 2 | 3 | /5/1/1 | 1 | 8 | 2 |
|----|-------|---------|-----|-------|------|

See this question also for related material.

Answer 2

declare @ex nvarchar(max) = '[ 7,2, [6,7], 2, 10 ]';
;WITH cte
AS (
 SELECT *
 FROM OPENJSON(@ex, '$') AS j1
)
SELECT c.[key], ISNULL(v.value, c.value)
FROM cte c
 OUTER APPLY (
 SELECT *
 FROM OPENJSON(c.value, '$') AS j1
 WHERE c.[type] = 4
 )v
;