By Evan Carroll


2019-07-11 14:20:28 8 Comments

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.

2 comments

@Peter Vandivier 2019-07-11 15:21:04

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.

@Denis Rubashkin 2019-07-11 14:35:00

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
;

Related Questions

Sponsored Content

1 Answered Questions

Is the MySQL JSON data type bad for performance for data retrieval?

1 Answered Questions

3 Answered Questions

[SOLVED] Dynamic conversion of long data to wide with multiple columns

0 Answered Questions

List JSON array in MySQL as rows

  • 2017-11-09 12:14:41
  • Prabhat
  • 7168 View
  • 1 Score
  • 0 Answer
  • Tags:   mysql json

1 Answered Questions

[SOLVED] How to remove known elements from a JSON[] array in PostgreSQL?

2 Answered Questions

[SOLVED] Import poorly formatted CSV/JSON to SQL Server

3 Answered Questions

[SOLVED] Working with JSON data MySQL

  • 2017-01-15 15:02:12
  • Anthony S.
  • 2210 View
  • 0 Score
  • 3 Answer
  • Tags:   mysql json

1 Answered Questions

[SOLVED] Can SQL Server 2016 validate JSON on insert?

1 Answered Questions

[SOLVED] PostgreSql : flatten json array data

2 Answered Questions

[SOLVED] Can I find all JSON scalars in PostgreSQL 9.3?

Sponsored Content