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: