PostgreSQL query by JSON object in array

Question

Folks,

I have a following table in my PostgreSQL.

CREATE TABLE public.my_table
(
    id uuid NOT NULL,
    name character varying(50) NOT NULL,
    field1 jsonb NOT NULL
)

I keep JSON array in my field1 as per example below:

[
    {
        "id": "abc"
    },
    {
        "id": "def"
    },
    {
        "id": "ghi"
    }
]

My question is: How do I query for rows that include a specific "id" in that JSON array?

Thanks for your help! Cheers!

jjanes · Accepted Answer · 2020-04-28 16:21:28Z

1

You can use the contains operator:

select * from my_table where field1 @> '[{"id":"whatever"}]'

This operation is able to make use of an index on field1, while a method that relies on jsonb_array_elements cannot be indexed.

answered Apr 28, 2020 at 16:21

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

1 Comment

This is even better than the one from @GMB. I love it! Thanks a lot!

GMB · Accepted Answer · 2020-04-27 23:12:44Z

1

One option uses exists and jsonb_array_elements():

select *
from my_table t
where exists (
    select 1 from jsonb_array_elements(t.field1) f(obj) where f.obj ->> 'id' = 'abc'
)

answered Apr 27, 2020 at 23:12

GMB

224k25 gold badges103 silver badges151 bronze badges

Great stuff! That's what I need. Thx mate!