In my PostgreSQL database I have the following schema:
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"}]');
and the following query:
select * from referral_datas where referrals @> '[{"risk_score": "2"}]'
which returns following results:
id | referrals
----------------------------------------
2 | [{"risk_score":"2"}]
Query works fine but I want have a query that will find referral datas with risk_score = 2
or risk_score = 1
so the result of the query should be:
id | referrals
----------------------------------------
1 | [{"risk_score":"1"}]
2 | [{"risk_score":"2"}]
How can I do this in PostgreSQL?
Here is db fiddle that You can experiment with:
You could expand jsonb array before filtering. Use jsonb_array_elements
to it:
SELECT
*
FROM
referral_datas c
JOIN jsonb_array_elements(c.referrals) AS foo(bar) ON TRUE
WHERE
foo.bar->>'risk_score' IN ('1', '2');
Please note that this probably won't use any index you've created on referral_datas.referrals
. That's ok if your data isn't so huge. Use carefully.