Search code examples
postgresqljsonb

Postgres JSONB SELECT Query with combined condition for a json array

Using PostgreSQL 14 I have a table 'fruits' with a JSONB column named 'items' containing this sample data:

[{"anz": 4, "typ": "Banana"}, {"anz": 5, "typ": "Apple"}, {"anz": 2, "typ": "Grapefruit"}]
[{"anz": 1, "typ": "Banana"}, {"anz": 1, "typ": "Apple"}, {"anz": 3, "typ": "Grapefruit"}]

this works:

SELECT * FROM fruits WHERE items @> '[{"typ":"Apple"}]';
SELECT * FROM fruits WHERE (items -> 0 ->> 'lvl')::int > 4;

Bu now I would like to fetch only the record where Apple has 'anz > 3'. Combining the WHERE clauses from the queries above doesn't fit of course. What's the appropriate SQL?

Expected Output should be the identified record (SELECT * FROM .. WHERE ..)

Solution

  • One option is to extract the array into elements by using json_array_elements() then apply your conditions:

    SELECT i.value as item
FROM fruits f
CROSS JOIN jsonb_array_elements(items) i
WHERE f.items @> '[{"typ":"Apple"}]'
      AND (i->>'anz')::int > 3
      AND i->>'typ' = 'Apple';

    Results :

    item
{"anz": 5, "typ": "Apple"}

    Demo here