PostgreSQL searching by jsonb fields

Question

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:

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

Michel Milezzi Michel Milezzi · Accepted Answer · 2018-10-17T11:23:41

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.

PostgreSQL searching by jsonb fields

2 Answers