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
;