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 people (data jsonb);
insert into people values ('{"bar":["def"],"foo":["abc","hij"]}'::jsonb);

CREATE TABLE

INSERT 0 1

create or replace function js_match(record jsonb, template jsonb) returns bool as $$
   select not exists (select 1 from jsonb_each(record) t1 
   full outer join jsonb_each(template) t2 on t1.key = t2.key 
   where t1.key is null or t2.key is null or not exists 
     (select 1 from jsonb_array_elements(t1.value) v where v = t2.value))
$$ language sql;

CREATE FUNCTION

select * from people where js_match(data, '{"bar":"def", "foo":"abc"}'::jsonb)

data

{"bar": ["def"], "foo": ["abc", "hij"]}

SELECT 1

select * from people where js_match(data, '{"bar":"def", "foo":"hij"}'::jsonb)

data

{"bar": ["def"], "foo": ["abc", "hij"]}

SELECT 1

select * from people where js_match(data, '{"bar":"def"}'::jsonb)

data

SELECT 0

select * from people where js_match(data, '{"foo":"abc"}'::jsonb)

data

SELECT 0

select * from people where js_match(data, '{"bar":"def", "foo":"abc", "xyz":"123"}'::jsonb)

data

SELECT 0