Поиск в PostgreSQL по полям jsonb

В моей базе данных 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?

Вот диджейская скрипка, с которой вы можете поэкспериментировать:

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

Источник

2 ответа

Решение

Вы можете расширить массив 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, Это нормально, если ваши данные не такие большие. Используйте осторожно.

Источник

Я пришел с этим запросом:

SELECT id,referrals FROM referral_datas
WHERE referrals->0->>'risk_score' IN ('1','2');
Источник
Другие вопросы по тегам