CODEWITHSUNDEEP
arrayspostgresqljsonb
fritz
fritz

Reputation: 163

Query for multiple array elements in Postgres JSONB column

I have a table mydata jsonb column containing an array of integers.

create table mydata
(
    name varchar,
    data jsonb
);

Here's some test data:

insert into mydata (name, data)
VALUES
('hello1', '[1]'),
('hello12', '[1,2]'),
('hello2', '[2]'),
('hello23', '[2,3]')
;

I now would like to query the table for elements, that contain either 2 or 3 (or both) in "data". Is there a better syntax for this besides:

select * from mydata where (data @> '2' or data @> '3');

because it could be that I have more than 2 options to query for of course. I would assume that I'm able to do a subquery like this (not working, just as hint what I'd like to achieve):


create table other ( id bigserial , text varchar);
insert into other (id, text) values (1, 'x'), (2, 'y'), (3, 'y'), (4, 'z');

What I now want to do is, get all data from mydata where data has a reference to other_table

select * from mydata where (data @> IN (select distinct id from other_table where text='y'));

Thanks a lot, Fritz

Upvotes: 1

Views: 1312

Answers (2)

user330315
user330315

Reputation:

If the IDs come from a different table, you could do something like this:

select *
from mydata m
where exists (select *
              from other o
              where o.id in (select v::int 
                             from jsonb_array_elements_text(m.data) as x(v)))

With Postgres 12 or later, you could use

select *
from mydata m
where exists (select *
              from other o
              where jsonb_path_exists(m.data, '$[*] ? (@ == $id)', jsonb_build_object('id', o.id)))

Not sure which one would be faster.

Upvotes: 0

S-Man
S-Man

Reputation: 23756

step-by-step demo:db<>fiddle

SELECT DISTINCT                             -- 3
    name,
    data
FROM mydata,
   jsonb_array_elements_text(data) elems    -- 1
WHERE value::int IN (
    SELECT id FROM other WHERE "text" = 'y' -- 2
)
  1. Extract all array elements into an own record
  2. Filter former elements whether they are elements of the subquery
  3. Because this filter could return same records twice (if the original data match both, 2 and 3) the DISTINCT ensures to return unique records.

Upvotes: 1

Related Questions