Postgres jsonb contains @> should consider duplicates

userC6Fx 0
Viewed 45

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.

database postgresql
1 Answers

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

qaziqarta0
Related