CODEWITHSUNDEEP
postgresqljsonb
Rudi
Rudi

Reputation: 1179

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

Upvotes: 1

Views: 1001

Answers (2)

Zegarek
Zegarek

Reputation: 26322

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

You can speed this up by adding an index:

create index on fruits using gin(items jsonb_path_ops);

The doc isn't immediately clear on whether your expression should be able to use it:

For these operators, a GIN index extracts clauses of the form accessors_chain = constant out of the jsonpath pattern, and does the index search based on the keys and values mentioned in these clauses. The accessors chain may include .key, [*], and [index] accessors. The jsonb_ops operator class also supports .* and .** accessors, but the jsonb_path_ops operator class does not.

But it does.

Upvotes: 2

SelVazi
SelVazi

Reputation: 16063

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

Upvotes: 1

Related Questions