sql - PostgreSQL 按 jsonb 字段搜索

Question

在我的 PostgreSQL 数据库中，我有以下架构:

CREATE TABLE referral_datas (
    id integer,
    referrals jsonb
);

INSERT INTO referral_datas (id, referrals)
  VALUES (1, '[{"risk_score": "1"}]');

INSERT INTO referral_datas (id, referrals)
  VALUES (2, '[{"risk_score": "2"}]');

INSERT INTO referral_datas (id, referrals)
  VALUES (3, '[{"risk_score": "3"}]');

和以下查询:

select * from referral_datas where referrals @> '[{"risk_score": "2"}]'

返回以下结果:

    id | referrals
----------------------------------------
    2  | [{"risk_score":"2"}]

查询工作正常，但我想要一个查询来查找具有 risk_score = 2 或 risk_score = 1 的推荐数据

所以查询的结果应该是:

    id | referrals
----------------------------------------
    1  | [{"risk_score":"1"}]
    2  | [{"risk_score":"2"}]

我如何在 PostgreSQL 中执行此操作？

这是您可以试验的 db fiddle:

https://www.db-fiddle.com/f/pP3AudKgUs242YJjR9mxaS/2

Answer 1

您可以在过滤之前展开 jsonb 数组。对它使用 jsonb_array_elements:

SELECT 
    * 
FROM 
    referral_datas c 
    JOIN jsonb_array_elements(c.referrals) AS foo(bar) ON TRUE
WHERE
    foo.bar->>'risk_score' IN ('1', '2');

请注意，这可能不会使用您在 referral_datas.referrals 上创建的任何索引。如果您的数据不是那么庞大，那也没关系。谨慎使用。