SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
declare @ex nvarchar(max) = '[ 7,2, [6,7], 2, 10 ]';


SELECT *
FROM OPENJSON(@ex, '$') AS j1;


key
value
type


0
7
2

1
2
2

2
[6,7]
4

3
2
2

4
10
2


declare @ex nvarchar(max) = '[ 7,2, [6,7], 2, 10 ]';

-- naive solution
SELECT 
    j1.[key] as [1key],
    j2.[key] as [2key],
    coalesce(j2.value,j1.value)
FROM OPENJSON(@ex, '$') AS j1
OUTER APPLY OPENJSON(iif(j1.type = 4,j1.value,null), '$') AS j2;


1key
2key
(No column name)


0
null
7

1
null
2

2
0
6

2
1
7

3
null
2

4
null
10


declare @ex nvarchar(max) = '[ 7,2, [6,7], 2, 10 ]';

with j as (
    select [key] as topkey, *, 1 as level 
    from openjson(@ex) a
    union all 
    select j.[key], b.*, j.level + 1 
    from j
    cross apply openjson(j.value) b
    where j.type in (4,5)
       and j.level < 50 --maxrecursion
)
select [topkey],level, [key], [value] 
from j
where type not in (4,5)
order by topkey--level, [key]


topkey
level
key
value


0
1
0
7

1
1
1
2

2
2
0
6

2
2
1
7

3
1
3
2

4
1
4
10