Postgresql:如何查询包含某些值的 JSONb 数组

Question

我有这张表:

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

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

我想要的是 postgres 中的 JSONb 查询，当我需要查找 fir 示例任何包含“civil”OR“derecho”的条目

Answer 1

终于找到了一个方法来做到这一点:

直接将json数组存储为顶层:

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

SELECT * 
FROM lawyer 
WHERE data ? 'penal';

结果: