Postgres: Searching all sub-arrays of an array

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?

Answer

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