Find object by key/value in an array in postgresql jsonb column

Question

I have the following table:

CREATE TABLE api_data (
    id bigserial NOT NULL PRIMARY KEY,
    content JSONB NOT NULL
);

Now I insert an array like this into the content column:

[{ "id": 44, "name": "address One", "petId": 1234 },
 { "id": 45, "name": "address One", "petId": 1234 },
 { "id": 46, "name": "address One", "petId": 1111 }]

What I want next is to get exactly the objects that have the "petId" set to a given value.

I figured I could do

select val 
from api_data 
WHERE content @> '[{"petId":1234}]'

But that returns the whole array.

Another thing I found is this query:

select val 
from api_data 
   JOIN LATERAL jsonb_array_elements(content) obj(val) ON obj.val->>'petId' = '1234' 
WHERE content @> '[{"petId":1234}]'

Which returns the object I am looking for, but three times which matches the number of elements in the array.

What I actually need is a result like this:

[{ "id": 44, "name": "address One", "petId": 1234 },
 { "id": 45, "name": "address One", "petId": 1234 }]

Right now 9.6, but I guess upgrading would not be a problem. — sveri
– sveri, Commented Jun 22, 2020 at 11:58

user330315 · Accepted Answer · 2020-06-22 11:53:03Z

4

If you are using Postgres 12, you can use a JSON path expression:

select jsonb_path_query_array(content, '$[*] ? (@.petId == 1234)') as content
from api_data
where content @> '[{"petId":1234}]';

If you are using an older version, you need to unnest and aggregate manually:

select (select jsonb_agg(e)
        from jsonb_array_elements(d.content) as t(e)
        where t.e @> '{"petId":1234}') as content
from api_data d
where d.content @> '[{"petId":1234}]'

answered Jun 22, 2020 at 11:53

user330315

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

Collectives™ on Stack Overflow

Find object by key/value in an array in postgresql jsonb column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related