QUESTION

Is there a way to do SQL wildcard/globbing searches on the contents of an array?  

For example some users can assume multiple roles in multiple AWS accounts.  If I want to use p_any_aws_arns for the performance benefits, how can I search that array for the equivalent of LIKE arn:aws:sts::%:assumed-role/%/[email protected]?

ANSWER 

Yes, wildcard searches on the contents of an array are possible. See the example query that uses lateral flatten in the Data Analytics documentation.

Additional information from Snowflake's documentation:

• Lateral Join and Lateral Flatten

• Flatten Docs