UNNEST on Empty Array does not seem to be supported ( multiselect component )

I am trying to use multiselect with big query, and the provided suggestion in documentation to handle empty value ( no option selected ) in multiselect does not seem to be valid while using UNNEST
While empty values in dropdowns are handled by including {{!input.value}} OR column={{input.value}} this does not work for UNNEST, is there any work around to make UNNEST work with empty arrays, I've tried CASE and IF scripting to make UNNEST conditional but seems like that part of query is always executed.

Hi @hardeepmonty!

I don't think that BigQuery actually ignores everything after the or, so it doesn't like to unnest an empty array.

This might work for you :blush:

select * from <TABLE_NAME_HERE> where {{ !multiselect1.value.length }} or customer.full_company_name IN UNNEST({{ !multiselect1.value.length ? [""] : multiselect1.value }})

3 Likes

Hi there,

Thanks for the workaround, it works fine.

That being said, the following big query works well in the big query console:

SELECT * FROM `bigquery-public-data.census_bureau_usa.population_by_zip_2010` 
WHERE zipcode IN UNNEST([])
LIMIT 10

Is it possible that there is a bug on your end?

I'm glad the workaround works! It's definitely related to how Retool handles arrays with Big Query resources. It's also making sure to properly handle any situations where arrays are empty.

Since this is fairly unique to Retool, we have some docs on arrays + different resource types!

https://docs.retool.com/docs/sql-query-faq#how-can-i-use-arrays-in-sql-queries

1 Like