Postgresql: How to query for JSONb arrays containing some value(s)

Question

I have this table:

CREATE TABLE lawyer (
  id SERIAL PRIMARY KEY,
  data jsonb
);

INSERT INTO lawyer (data) VALUES
    ('{"a": 1}'),
    ('{"tags":["derecho", "civil", "laboral", "penal"]}'),
    ('{"tags":["derecho", "penal"]}')
;

What I want is a JSONb query in postgres for when I need to find fir example any entry that contains "civil" OR "derecho"

Possible duplicate of Postgres JSONB: query values in JSON array — bjmc
– bjmc, Commented Jun 9, 2016 at 18:29

Jeka · Accepted Answer · 2016-06-09 18:13:42Z

2

Finally found a way to do this:

Store json arrays directly as top level:

INSERT INTO lawyer (data) VALUES
    ('["derecho","laboral","penal"]'),
    ('["derecho", "civil", "laboral", "penal"]'),
    ('["derecho", "penal"]')
;

SELECT * 
FROM lawyer 
WHERE data ? 'penal';

Result:

answered Jun 9, 2016 at 18:13

Jeka

1,7103 gold badges23 silver badges38 bronze badges

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

Comments

ChrisJ · Accepted Answer · 2016-09-06 09:56:12Z

2

For those looking for an answer for the original data structure, here's the SQL:

SELECT * FROM lawyer WHERE lawyer.data->'tags' ? 'penal'

answered Sep 6, 2016 at 9:56

ChrisJ

2,8701 gold badge24 silver badges41 bronze badges

Collectives™ on Stack Overflow

Postgresql: How to query for JSONb arrays containing some value(s)

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related