Reputation: 23
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!
Upvotes: 1
Views: 160
Reputation: 44373
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.
Upvotes: 1
Reputation: 222652
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'
)
Upvotes: 1