Skip to main content
Panther Knowledge Base

How do I do SQL wildcard searching on the contents of an array in Panther?

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/%/first.last@email.com?

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: