Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

create table tbl (items jsonb);
insert into tbl values ('[{"type": "human", "name": "Alice"}, {"type": "dog", "name": "Fido"}, {"type": "dog", "name": "Pluto"}]');

CREATE TABLE

INSERT 0 1

select t.* from tbl t where exists (select 1 from jsonb_array_elements(t.items) v 
   cross join jsonb_array_elements('[{"type": "human", "name": "Alice"}]'::jsonb) v1 
   where v.value = v1.value)

items

[{"name": "Alice", "type": "human"}, {"name": "Fido", "type": "dog"}, {"name": "Pluto", "type": "dog"}]

SELECT 1

create or replace function get_results(param jsonb) 
  returns table(items jsonb)
as $$
  select t.* from tbl t where exists (select 1 from jsonb_array_elements(t.items) v 
   cross join jsonb_array_elements(param) v1 
   where v.value = v1.value)
$$ language sql;

CREATE FUNCTION

select * from get_results('[{"type": "human", "name": "Alice"}]'::jsonb)

items

[{"name": "Alice", "type": "human"}, {"name": "Fido", "type": "dog"}, {"name": "Pluto", "type": "dog"}]

SELECT 1

select * from get_results('[{"type": "human", "name": "Alice"}, {"type": "dog", "name": "Doggy"}]'::jsonb)

items

[{"name": "Alice", "type": "human"}, {"name": "Fido", "type": "dog"}, {"name": "Pluto", "type": "dog"}]

SELECT 1