3 add 2-level nest example & PATH, add results tables, link to @MichaelGreen 's recent similar question edited Jul 11 at 15:36 Peter Vandivier 2,85611 gold badge1212 silver badges3030 bronze badges 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 | |----|-------|---------|-----|-------|------| `````` 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. 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. 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 | |----|-------|---------|-----|-------|------| `````` 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. 2 add 2-level nest example & PATH edited Jul 11 at 15:31 Peter Vandivier 2,85611 gold badge1212 silver badges3030 bronze badges 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. ``````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 [key] x.i as topkeyid,  *, convert(int,1) as level[level], convert(nvarchar(max),N'/')+convert(nvarchar(max),a.[key]) as [path], a.[key], a.[value], a.[type] from @ex x cross apply openjson(@exx.r) a union all select j.[key] id,  b j.*[level] + 1, j.level[path] + 1convert(nvarchar(max),N'/')+convert(nvarchar(max),a.[key]), a.[key], a.[value], a.[type] from j cross apply openjson(j.value) ba where j.type in (4,5) and j.level < 50 -- your maxrecursion level ) select [topkey],level, [key], [value] * from j where type not in (4,5) order by topkey;id, [path]; `````` 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. DB Fiddle ``````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 -- your maxrecursion level ) select [topkey],level, [key], [value] from j where type not in (4,5) order by topkey; `````` 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. 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]; `````` 1 answered Jul 11 at 15:21 Peter Vandivier 2,85611 gold badge1212 silver badges3030 bronze badges 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. DB Fiddle ``````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 -- your maxrecursion level ) select [topkey],level, [key], [value] from j where type not in (4,5) order by topkey; ``````