In postgresql how can I select rows where a jsonb array contains objects?

Question

My database table is something like this (data is a JSONB column):

 id |                 data                 
----+--------------------------------------
  1 | {"tags": [{"name": "tag1"}, {"name": "tag2"}]}
  2 | {"tags": [{"name": "tag2"}]}
  3 | {"tags": [{"name": "tag3"}]}
  4 | {"tags": [{"name": "tag4"}]}

I'd like to write a query that will return the rows where data contains tags tag2 or tag3. So rows 1, 2, and 3 should be returned. I've been looking at the postgresql JSONB documentation and it's not clear to me how to query a nested structure like this. How would I write the where clause?

Haleemur Ali · Accepted Answer · 2018-07-10 14:39:30Z

19

Using where exists with a filter on the unnested json array will return the rows with id 1, 2 & 3

SELECT * 
FROM mytable
WHERE EXISTS (
    SELECT TRUE 
    FROM jsonb_array_elements(data->'tags') x 
    WHERE x->>'name' IN ('tag2', 'tag3')
)

answered Jul 10, 2018 at 14:39

Haleemur Ali

28.6k6 gold badges67 silver badges89 bronze badges

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

Comments

Sami Kuzey · Accepted Answer · 2019-07-23 08:29:02Z

18

jsonb_array_elements would harm the performance. If that is a concern, you can use this query instead

SELECT * FROM MyTable where 
    data @> '[{"name": "tag2"}]'::jsonb 
    or 
    data @> '[{"name": "tag3"}]'::jsonb;

answered Jul 23, 2019 at 8:29

Sami Kuzey

1812 silver badges3 bronze badges

2 Comments

Chris Magnuson Over a year ago

How does this work without having to specify the top level field tags? What if there was another top level field that was also an array, would it search within that as well?

Mistic Over a year ago

@ChrisMagnuson I tested it and it seems to work as expected : data @> '{"tags":[{"name": "tag2"}]}'::jsonb It does a deep partial-match postgresql.org/docs/16/datatype-json.html#JSON-CONTAINMENT

Vao Tsun · Accepted Answer · 2018-07-10 14:15:55Z

let's say:

db=# create table so33(id int, data jsonb);
CREATE TABLE
db=# copy so33 from stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>   1 | {"tags": [{"name": "tag1"}, {"name": "tag2"}]}
  2 | {"tags": [{"name": "tag2"}]}
  3 | {"tags": [{"name": "tag3"}]}
  4 | {"tags": [{"name": "tag4"}]}>> >> >>
>> \.
COPY 4

then:

db=# with c as (select *,jsonb_array_elements(data->'tags')->>'name' e from so33)
select * from c where e in ('tag2','tag3');
 id |                      data                      |  e
----+------------------------------------------------+------
  1 | {"tags": [{"name": "tag1"}, {"name": "tag2"}]} | tag2
  2 | {"tags": [{"name": "tag2"}]}                   | tag2
  3 | {"tags": [{"name": "tag3"}]}                   | tag3
(3 rows)

A simple jsonb_array_elements should do

obviously SELECT DISTINCT id, data FROM e should give your expected result

Collectives™ on Stack Overflow

In postgresql how can I select rows where a jsonb array contains objects?

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related