How to use array variable type in SQL question

7

It took me way too long to work out & the docs make no mention of how to handle it at all, so hopefully this helps someone else:

I want to filter by a list of UUIDs (tho it’s the same for any list of strings).

I wanted to be able to paste a list like 00000000-0000-0000-0000-000000000000, 00000000-0000-0000-0000-000000000001 into the variable field (but this also supports space-delimited lists).

select id
from my_table
where true
	and id in (
		select regexp_split_to_table({{ids}}, '[, ]+')::uuid
	)
;

{{ids}} is the variable, set it to type “Text”

regexp_split_to_table converts the quoted string metabase passes (which cannot be changed :downcast_face_with_sweat:) into records, based on the delimiter.

'[, ]+' as the delimiter means split the string on 1 or more commas or spaces

::uuid is casting the string to UUID; you may need to remove it or cast to something else.

I couldn’t get any other = any( {{ids}} )or in ( {{ids}} )style incantation to work.