Can query lambdas be used as a set of values for an IN statement

Open Q & A Querying your data
1

:question:Question
Is it possible for a query lambda parameter to be used as a set of values for an IN statement? Like:

SELECT * FROM table WHERE column IN :lambda_param

:white_check_mark:Answer
It is, if you pass in a list for the “lambda_param”.

Parameter Name: list
Type: String
Value: a|b|c|d|e

On the query side, you can write something like this:

SELECT * FROM table WHERE ARRAY_CONTAINS(SPLIT(:list, '|'), column)
2

While a simple delimiter might work for simple cases, if you every have to deal with quotes or other complex strings, a reliable way to encode lists would be to encode that into a JSON string and then use JSON_PARSE() in your query:

SELECT .... WHERE ARRAY_CONTAINS(JSON_PARSE(:param), column)

:param just needs to be of type string holding a JSON encoded array such as
'["foo", "bar", "foo,bar", "\"foo\""]'

1 Like