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](http://stackoverflow.com/questions/32357971/postgres-jsonb-query-values-in-json-array) — bjmc, Jun 09 '16 at 18:29

score 2 · Answer 1 · answered Jun 09 '16 at 18:13

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:

score 2 · Answer 2 · answered Sep 06 '16 at 09:56

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 06 '16 at 09:56

ChrisJ

2,486
21
40

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

2 Answers2