Select "WHERE IN" with PostgreSQL and JSONB

Question

Given table_a like this:

 id | name
----+------
  1 | aaaa
  2 | bbbb
  3 | cccc

I can obviously issue the following query:

SELECT * FROM table_a WHERE name IN ('aaaa', 'bbb');

But given table_b like this:

 id |       data
----+------------------
  1 | {"name": "aaaa"}
  2 | {"name": "bbbb"}
  3 | {"name": "cccc"}

How do I issue a query "give me all the rows where the value of the key name is contained in this list of values?"

I know I can use the jsonb operator @> to check for each combination, but unfortunately I'd have to issue as many queries as the number of values I want to check against. Is there a way to do it in one query?

UPDATE:

I found a solution right away:

select * from table_b where data #>> '{name}' IN ('aaaa', 'bbb');

Alex Gaynor · Accepted Answer · 2015-09-10 21:51:18Z

19

SELECT * FROM table_a WHERE data->>'name' IN ('aaaa', 'bbbb')

seems like it's what you want?

answered Sep 10, 2015 at 21:51

Alex Gaynor

15.1k10 gold badges66 silver badges113 bronze badges

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

2 Comments

rhymes Over a year ago

Thanks, I found a similar solution in the meantime :D I'll leave the question here because I had a hard time finding it with the usual keywords on google. Apparently they have a similar query plan also.

Erwin Brandstetter Over a year ago

@GuybrushThreepwood: Since the answer is correct and what you needed, consider accepting it. json / jsonb operators in the manual.

Collectives™ on Stack Overflow

Select "WHERE IN" with PostgreSQL and JSONB

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related