Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--https://stackoverflow.com/q/77961954/5298879
create table fruits (id serial, items jsonb);
insert into fruits(items) values
('[{"anz": 4, "typ": "Banana"}, {"anz": 5, "typ": "Apple"}, {"anz": 2, "typ": "Grapefruit"}]'),
('[{"anz": 1, "typ": "Banana"}, {"anz": 1, "typ": "Apple"}, {"anz": 3, "typ": "Grapefruit"}]')
returning *

CREATE TABLE

id
items

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

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

INSERT 0 2

SELECT * FROM fruits WHERE items @> '[{"typ":"Apple"}]';

id
items

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

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

SELECT 2

SELECT * FROM fruits WHERE (items -> 0 ->> 'lvl')::int > 4;

id
items

SELECT 0

/* But 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? */
--https://stackoverflow.com/a/77962251/5298879
select * from fruits where items @? '$[*] ? (@.typ=="Apple" && @.anz>3)';

id
items

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

SELECT 1

--here are the things found by that jsonpath expression
select id,jsonb_path_query(items, '$[*] ? (@.typ=="Apple" && @.anz>3)') from fruits

id
jsonb_path_query

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

SELECT 1

--test to see if it'll use GIN index
--300k entries of 26 fruits with 0-20 "anz", collected into about 50k rows
select setseed(.42);

insert into fruits(items)
select jsonb_agg(distinct fruit)
from (select (random()*5e4)::int as basket,
             jsonb_build_object('anz',(random()*20)::int,
                                'typ',('{Banana, Apple, Grapefruit, Mango,
                                        Kiwi, Coconut, Pineapple, Lemon,
                                        Satsuma, Peach, Tangerine, Orange,
                                        Kiwano, Avocado, Lychee, Pear,
                                        Plum, Melon, Watermelon, Pomelon,
                                        Blueberry, Raspberry, Blackberry,
                                        Winterberry, Redberry, Cherry
                                        }'
                                       ::text[])[1+(random()*25)::int]) fruit
      from generate_series(1,3e5)g)
group by basket;

setseed

SELECT 1

ERROR:  could not write to file "base/pgsql_tmp/pgsql_tmp784.1": No space left on device

--before building the index
explain analyze verbose
select * from fruits where items @? '$[*] ? (@.typ=="Apple" && @.anz>3)';

QUERY PLAN

Seq Scan on public.fruits  (cost=0.00..25.88 rows=13 width=36) (actual time=0.021..0.024 rows=1 loops=1)

  Output: id, items

  Filter: (fruits.items @? '$[*]?(@."typ" == "Apple" && @."anz" > 3)'::jsonpath)

  Rows Removed by Filter: 1

Planning Time: 0.050 ms

Execution Time: 0.044 ms

EXPLAIN

create index on fruits using gin(items jsonb_path_ops);

CREATE INDEX

vacuum analyze fruits;

VACUUM

--index is being used, 4x faster results
explain analyze verbose
select * from fruits where items @? '$[*] ? (@.typ=="Apple" && @.anz>3)';

QUERY PLAN

Seq Scan on public.fruits  (cost=0.00..1.02 rows=1 width=158) (actual time=0.008..0.011 rows=1 loops=1)

  Output: id, items

  Filter: (fruits.items @? '$[*]?(@."typ" == "Apple" && @."anz" > 3)'::jsonpath)

  Rows Removed by Filter: 1

Planning Time: 0.114 ms

Execution Time: 0.018 ms

EXPLAIN

--Here's a sample of what it had to deal with
select id,jsonb_pretty(items) 
from fruits tablesample bernoulli(.42)repeatable(.42)limit 10;

id
jsonb_pretty

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