Postgres 9.5

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

select version();


version


PostgreSQL 9.5.25 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit


SELECT 1

create table mytable (
  id serial primary key,
  tags jsonb not null default '{}'::jsonb
);

insert into mytable (tags) values ('{"coo": "IN", "owner": "Online", "vastxt": "", "ecc_ibd": "180000010", "hm_order": "400000010", "entitled_to_dispose": "W141"}');
insert into mytable (tags) values ('{"owner": "Online", "vastxt": "", "ecc_ibd": "180000010", "hm_order": "400000010", "entitled_to_dispose": "W141"}');
insert into mytable (tags) values ('{"coo": "IN", "vastxt": "", "ecc_ibd": "180000010", "hm_order": "400000010", "entitled_to_dispose": "W141"}');

select * from mytable;


id
tags


1
{"coo": "IN", "owner": "Online", "vastxt": "", "ecc_ibd": "180000010", "hm_order": "400000010", "entitled_to_dispose": "W141"}

2
{"owner": "Online", "vastxt": "", "ecc_ibd": "180000010", "hm_order": "400000010", "entitled_to_dispose": "W141"}

3
{"coo": "IN", "vastxt": "", "ecc_ibd": "180000010", "hm_order": "400000010", "entitled_to_dispose": "W141"}


SELECT 3

with search_terms as (
  select array_agg(el) as search_array
    from jsonb_array_elements_text('["IN", "Online"]') as et(el)
)
select m.id, m.tags
  from search_terms s
       cross join mytable m
       cross join lateral jsonb_each_text(tags) t(k,v)
 group by m.id, m.tags, s.search_array
having array_agg(t.v) @> s.search_array
;


id
tags


1
{"coo": "IN", "owner": "Online", "vastxt": "", "ecc_ibd": "180000010", "hm_order": "400000010", "entitled_to_dispose": "W141"}


SELECT 1

`id`	`tags`
`1`	`{"coo": "IN", "owner": "Online", "vastxt": "", "ecc_ibd": "180000010", "hm_order": "400000010", "entitled_to_dispose": "W141"}`
`2`	`{"owner": "Online", "vastxt": "", "ecc_ibd": "180000010", "hm_order": "400000010", "entitled_to_dispose": "W141"}`
`3`	`{"coo": "IN", "vastxt": "", "ecc_ibd": "180000010", "hm_order": "400000010", "entitled_to_dispose": "W141"}`