Search code examples
sqlarrayspostgresqljsonb

PostgreSQL array of object intersection

Given I have rows in my database, with a JSONB column that holds an array of items as such:

[
  {"type": "human", "name": "Alice"},
  {"type": "dog", "name": "Fido"},
  {"type": "dog", "name": "Pluto"}
]

I need to be able to query rows based on this column. The query I want to write is a check to see if my array argument intersects, at any point, with this column.

Eg:

  • If I search for [{"type": "human", "name": "Alice"}], I should get a hit.
  • If I search for [{"type": "human", "name": "Alice"}, {"type": "dog", "name": "Doggy"}] I should also get a hit (Since one of the objects intersects)

I've tried using the ?| operator, but according to the docs, comparison is only made by keys. I need to match the entire jsonb object

Solution

  • You can use exists with cross join:

    select t.* from tbl t where exists (select 1 from jsonb_array_elements(t.items) v 
   cross join jsonb_array_elements('[{"type": "human", "name": "Alice"}, {"type": "dog", "name": "Doggy"}]'::jsonb) v1 
   where v.value = v1.value)

    See fiddle.

    As a function:

    create or replace function get_results(param jsonb) 
  returns table(items jsonb)
as $$
  select t.* from tbl t where exists (select 1 from jsonb_array_elements(t.items) v 
   cross join jsonb_array_elements(param) v1 
   where v.value = v1.value)
$$ language sql;

    See fiddle.