I had to tackle a similar issue and found that Redshift’s options for working with nested data are fairly limited. I explored two different approaches to make the data easier to query and analyze.

If your data is stored in S3 and accessible via Athena, one option is to create a new AWS Glue table where each object in the array is stored as a separate row. This structure makes filtering and searching much more straightforward.

In Redshift, I ended up doing something similar by using a stored procedure to parse the JSON array of objects and insert each element into a new, flattened table designed for reporting purposes.

I understand this doesn't exactly answer your question and requires more work but may be necessary if the JSON structure you are getting is dynamic.