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
select * from people p where not exists (select 1 from jsonb_each(p.data) t1
full outer join jsonb_each('{"bar":"def", "foo":"abc"}'::jsonb) 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))
| data |
|---|
| {"bar": ["def"], "foo": ["abc", "hij"]} |
SELECT 1