Postgres: Searching all sub-arrays of an array

Question

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?

Sounds like time to move this data to it's own table. That will be multiple orders of magnitude faster. — Joel Coehoorn
– Joel Coehoorn, Commented May 10, 2022 at 18:52

user330315 · Accepted Answer · 2022-05-10 19:01:28Z

1

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

answered May 10, 2022 at 19:01

user330315

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

Collectives™ on Stack Overflow

Postgres: Searching all sub-arrays of an array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related