Reputation: 329
I have a PostgreSQL table named data_audit
with a column named body
, which is of type json
. Each row contains a value with the following structure:
{
"target": {
"ids": [
"ID1",
"ID2"
]
}
}
(I've removed lots of irrelevant fields.)
I want to select all the rows whose ids
array (which may contain 0, 1 or more strings) includes a particular value.
I've tried various combinations of ::jsonb[]
, ANY()
, @>
, json_array_elements_text()
and more, but to no avail.
What's the simplest, most efficient way to run this query?
This table is not yet in production, so if it would be easier / more efficient if the field were of another type (jsonb
?), that is potentially an option.
I'm running PostgreSQL 10.3.
Thanks!
Upvotes: 0
Views: 345
Reputation: 31746
This is one appropriate way to query something you want.
select * FROM data_audit cross join lateral
json_array_elements_text (body->'target'->'ids') as j(id)
where j.id ='ID1';
Upvotes: 1