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

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, '

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.) AS j1

This gets you to,

%%CODEBLOCK_4%%

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.

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

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.

StackExchange Database Administrators Q#242662, answer score: 10