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

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

Answer

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)
                 );