PostgreSQL array of object intersection

Question

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

Ajax1234 · Accepted Answer · 2023-01-16 17:54:35Z

1

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.

edited Jan 16, 2023 at 17:54

answered Jan 16, 2023 at 17:48

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL array of object intersection

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related