Query for multiple array elements in Postgres JSONB column

Question

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

In your question you state that you want elements that contain "either 2 or 3" - which can be read that it shouldn't return elements that contain both - but that isn't what you query is doing. If you do want to also include elements that contain both values this would be easy with a native array as it supports the overlaps operator && - but JSONB doesn't support that. — user330315
– user330315, Commented Feb 9, 2021 at 7:36
@a_horse_with_no_name sorry for the misunderstanding: I want all entries that have 2 or 3 or both. will update description — fritz
– fritz, Commented Feb 9, 2021 at 8:06
If you only store integers in that column, I would recommend to use int[] instead of jsonb then this type of queries is way easier (where data && array[2,3]) — user330315
– user330315, Commented Feb 9, 2021 at 8:07
@a_horse_with_no_name: yes, thanks! For this very specific usecase in question, this works perfect. Even with a subquery: ``` select * from mydata where data && array(select id from other where text = 'y'); ``` (assuming data has now datatype bigint[] Thanks — fritz
– fritz, Commented Feb 9, 2021 at 8:30

S-Man · Accepted Answer · 2021-02-09 08:36:13Z

1

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
)

Extract all array elements into an own record
Filter former elements whether they are elements of the subquery
Because this filter could return same records twice (if the original data match both, 2 and 3) the DISTINCT ensures to return unique records.

edited Feb 9, 2021 at 8:36

answered Feb 9, 2021 at 7:46

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

3 Comments

fritz Over a year ago

thanks. Still this is requiring to put all entries I want to query for in the list manually. I would like to have a subquery returning any number of results. Is this possible?

S-Man Over a year ago

It's not clear to me what you want to achieve... Please add some sample data and the expected output to the question

fritz Over a year ago

updated the question with a second sample table. Should be clear now hopefully.

score 0 · Accepted Answer · 2021-02-09 08:44:08Z

0

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.

edited Feb 9, 2021 at 8:44

answered Feb 9, 2021 at 8:38

user330315

Collectives™ on Stack Overflow

Query for multiple array elements in Postgres JSONB column

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related