PostgreSQL: exclude complete jsonb array if one element fails the WHERE clause

Question

Assume a table json_table with columns id (int), data (jsonb). A sample jsonb value would be

{"a": [{"b":{"c": "xxx", "d": 1}},{"b":{"c": "xxx", "d": 2}}]}

When I use an SQL statement like the following:

SELECT data FROM json_table j, jsonb_array_elements(j.data#>'{a}') dt WHERE (dt#>>'{b,d}')::integer NOT IN (2,4,6,9) GROUP BY id;

... the two array elements are unnested and the one that qualifies the WHERE clause is still returned. This makes sense since each array element is considered individually. In this example I will get back the complete row

{"a": [{"b":{"c": "xxx", "d": 1}},{"b":{"c": "xxx", "d": 2}}]}

I'm looking for a way to exclude the complete json_table row when any jsonb array element fails the condition

It is actually PostgreSQL 10.12. My mistake with the tags – Georgios Varisteas Feb 24 '20 at 07:59 — Georgios Varisteas, Feb 24 '20 at 07:59

score 0 · Accepted Answer · answered Feb 23 '20 at 22:54

You can move the condition to the WHERE clause and use NOT EXISTS:

SELECT data
FROM json_table j
WHERE NOT EXISTS (SELECT 1
                  FROM jsonb_array_elements(j.data#>'{a}') dt 
                  WHERE (dt#>>'{b,d}')::integer IN (2, 4, 6, 9)
                 );

score 0 · Answer 2 · edited Feb 24 '20 at 02:27

0

You can achieve it with the following query:

select data 
from json_table
where jsonb_path_match(data, '!exists($.a[*].b.d ? ( @ == 2 || @ == 4 || @ == 6 || @ == 9))')

edited Feb 24 '20 at 02:27

svelandiag

4,231
1
36
72

answered Feb 24 '20 at 01:09

Catalin M.

1,026
1
8
7

I cannot make this work. Whatever I try, I get is the following `ERROR: function jsonb_path_match(jsonb, unknown) does not exist` – Georgios Varisteas Feb 24 '20 at 07:56
I'm running PostgreSQL 10.12 actually – Georgios Varisteas Feb 24 '20 at 07:57
`jsonb_path_match()` requires Postgres 12 – Feb 24 '20 at 07:59

PostgreSQL: exclude complete jsonb array if one element fails the WHERE clause

2 Answers2