Find data from json/jsonb column by array in Postgresql

Question

I have a column with json/jsonb data. Database is postgresql.

column data sample

{"postId": "p522", "groupId": "g11", ...}
{"postId": "p5ds", "groupId": "g234", ...}
{"postId": "p5cz", "groupId": "g597", ...}

I am trying to search as an array of data from json/jsonb column.

What's best or right way to do it?

SELECT * FROM column_name
WHERE 
-- How i can do, similar to this? 
data #> '{groupId}' IN ('g11', 'g597')

-- this works but only for single. I am trying to find by array. 
meta @> '{"groupId": "g597"}'

Niebian · Accepted Answer · 2019-12-17 15:01:49Z

2

You could use operator ?| if you want something similar to IN. It works with jsonb type.

SELECT * FROM column_name
WHERE meta -> 'groupId' ?| array['g11', 'g597']

More info about json/jsonb operators

answered Dec 17, 2019 at 15:01

Niebian

867 bronze badges

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

Comments

7urkm3n · Accepted Answer · 2019-11-08 17:12:09Z

0

Solved:

SELECT * FROM column_name 
WHERE meta ->> 'groupId' IN ('g11', 'g597')

answered Nov 8, 2019 at 17:12

7urkm3n

6,3304 gold badges33 silver badges48 bronze badges

Collectives™ on Stack Overflow

Find data from json/jsonb column by array in Postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related