PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    You’re using jsonb, so you have the full support of JSONPath expressions: demo at db<>fiddle

    select * from fruits where items @? '$[*] ? (@.typ=="Apple" && @.anz>3)';
    id items
    1 [{"anz": 4, "typ": "Banana"}, {"anz": 5, "typ": "Apple"}, {"anz": 2, "typ": "Grapefruit"}]
    • @? operator checks if the jsonb returns anything for the jsonpath expression
    • $ is your top-level thing
    • [*] checks all elements of the array
    • ? is a filter, like a where
    • @ is the element considered by the filter
    • && is the logical AND operator
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search