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 · Accepted Answer · 2018-10-17 12:35:39Z

1

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.

edited Oct 17, 2018 at 12:35

answered Oct 17, 2018 at 11:23

Michel Milezzi

11.4k3 gold badges24 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hitobat · Accepted Answer · 2018-10-17 08:59:37Z

0

I came up with this query:

SELECT id,referrals FROM referral_datas
WHERE referrals->0->>'risk_score' IN ('1','2');

answered Oct 17, 2018 at 8:59

Hitobat

3,0771 gold badge19 silver badges14 bronze badges

1 Comment

Mateusz Urbański Over a year ago

referrals array can have multiple objects so this will not work.

Collectives™ on Stack Overflow

PostgreSQL searching by jsonb fields

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related