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

jsonsql server

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.

Best Answer

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.

`jsonb` in Postgres 9.4 or later

Consider the jsonb data type in Postgres 9.4 or later. The 'b' at the end stands for 'binary'. Among other things, there is an equality operator (=) for jsonb. Most people will want to switch.

Depesz blog about jsonb.

`json`

There is no = operator defined for the data type json, because there is no well defined method to establish equality for whole json values. But see below.

You could cast to text and then use the = operator. This is short, but only works if your text representation happens to match. Inherently unreliable, except for corner cases. See:

How to query a json column for empty objects?

Or you can unnest the array and use the ->> operator to .. get JSON object field as text and compare individual fields.

Test table

2 rows: first one like in the question, second one with simple values.

CREATE TABLE tbl (
   tbl_id int PRIMARY KEY
 , jar    json[]
);

INSERT INTO t VALUES
   (1, '{"{\"value\" : \"03334/254146\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f5\"}"
        ,"{\"value\" : \"03334/254147\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f6\"}"
        ,"{\"value\" : \"03334/254148\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f7\"}"}')

 , (2, '{"{\"value\" : \"a\", \"typeId\" : \"x\"}"
        ,"{\"value\" : \"b\", \"typeId\" : \"y\"}"
        ,"{\"value\" : \"c\", \"typeId\" : \"z\"}"}');

Demos

Demo 1

You could use array_remove() with text representations (unreliable).

SELECT tbl_id
     , jar, array_length(jar, 1) AS jar_len
     , jar::text[] AS t, array_length(jar::text[], 1) AS t_len
     , array_remove(jar::text[], '{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-f6"}'::text) AS t_result
     , array_remove(jar::text[], '{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-f6"}'::text)::json[] AS j_result
FROM   tbl;

Demo 2

Unnest the array and test fields of individual elements.

SELECT tbl_id, array_agg(j) AS j_new
FROM   tbl, unnest(jar) AS j   -- LATERAL JOIN
WHERE  j->>'value' <> '03334/254146'
AND    j->>'typeId' <> 'ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf5'
GROUP  BY 1;

Demo 3

Alternative test with row type.

SELECT tbl_id, array_agg(j) AS j_new
FROM   tbl, unnest(jar) AS j   -- LATERAL JOIN
WHERE  (j->>'value', j->>'typeId') NOT IN (
         ('03334/254146', 'ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf5')
        ,('a', 'x')
       )
GROUP  BY 1;

`UPDATE` as requested

Finally, this is how you could implement your UPDATE:

UPDATE tbl t
SET    jar = j.jar
FROM   tbl t1
CROSS  JOIN LATERAL (
   SELECT ARRAY(
      SELECT j
      FROM   unnest(t1.jar) AS j  -- LATERAL JOIN
      WHERE  j->>'value'  <> 'a'
      AND    j->>'typeId' <> 'x'
      ) AS jar
   ) j
WHERE  t1.tbl_id = 2              -- only relevant rows
AND    t1.tbl_id = t.tbl_id;

db<>fiddle here

About the implicit LATERAL JOIN:

About unnesting arrays:

How to preserve the original order of elements in an unnested array?

DB design

To simplify your situation consider an normalized schema: a separate table for the json values (instead of the array column), joined in a n:1 relationship to the main table.

Postgresql – find all JSON scalars in PostgreSQL 9.3

Horrible as it is, you could write a PL/PgSQL function that attempts to extract an element from the object, traps the exception if it fails, and returns true on success or false on exception.

Untested:

create or replace function json_isobject(obj json)
returns boolean immutable language plpgsql as $$
begin
    begin
        perform obj->'';
    exception
      when invalid_parameter_value then
        return false;
      when others then
        raise;
    end;
    return true;
end;
$$;

This'll be pretty inefficient, since it's creating a sub-transaction for each call.

Or you could just update to 9.4.

On 9.4 I can't reproduce your reported error. Trying to look up a key in a json scalar returns null.

test=> select ('{"k":"v"}'::json) -> 'k' -> 'blah';
 ?column? 
----------

(1 row)

... and the same with using ->> to dereference a scalar.

So I'm not sure if this function will work on 9.3, and it won't on 9.4. To be 9.4-compatible you'd have to test if current_setting('pg_version_num') >= 90400 and return a result based on json_typeof instead.

You might need to adapt it to use the same expression you use to trigger your reported error.

Sample Data

My attempt

Best Answer

Related Solutions

PostgreSQL – How to Remove Known Elements from JSON[] Array

jsonb in Postgres 9.4 or later

json