Postgres jsonb contains @> should consider duplicates-CodePudding

I am trying to figure out how to do "contains all including duplicate elements" in a postgres jsonb array.

I am currently using @> which is returning true when the duplicate elements aren't in the original array.

For example, I am looking for an operator where this query returns true:

select '[1, 2, 3]'::jsonb @> '[1, 2]';

But this query returns false

select '[1, 2, 3]'::jsonb @> '[1, 1]';

@> returns true for both.

CodePudding user response：

There's no json containment function that tests for containment of elements, respecting the elements' counts at the same time.

You can write such function yourself, e.g.:

create or replace function jsonb_full_contain(a jsonb, b jsonb) 
returns boolean language sql as $$ 
select not exists(
  select 1 from (
    select 't1' t, jsonb_array_elements(a) v
    union all
    select 't2', jsonb_array_elements(b) v
  ) tt
  group by v 
  having count(case when t='t1' then 1 end) < count(case when t='t2' then 1 end))
$$;

select jsonb_full_contain('[1,2,3]'::jsonb, '[1,1]'::jsonb); -- returns false
select jsonb_full_contain('[1,2,3]'::jsonb, '[1]'::jsonb); -- returns true

fiddle