I have a Postgres table with a json column, named raw_data
, that is structured like this:
[{"id":1234, "name":"John Doe", "purchases":12}, {"id":1234, "name":"Jane Doe", "purchases":11}]
The number of sub-arrays can differ. It may be 1 or 20 or any number.
I want to perform a query where I can return the entire table row if, say, 'John Doe' occurs in the raw_data
column. I already know that I can perform a query like this:
select * from my_table where raw_data->0->> 'name' = 'John Doe'
But this won't work since the int (zero in this example) is specifying the nth sub-array of the array, and I won't know which sub-array to look in ahead of time.
Is there a way to specify a search through all sub-arrays?
CodePudding user response:
You can use a JSON path expression:
select *
from my_table
where raw_data @@ '$[*].name == "John Doe"';
This assumes that raw_data
is a jsonb
column (which it should be). If it's not, you need to cast it raw_data::jsonb