Need Help with "Parameter type must be provided for empty array values" Error in BigQuery Integration

App Building
, ,
1

Hi Retool Community,

I've been struggling all day to resolve an issue related to handling empty arrays in a BigQuery query within Retool, and I'm hoping to get some guidance from the community.

No matter what approach I take, I keep encountering the following error: "Parameter type must be provided for empty array values."

I've tried various workarounds, including conditional logic, using UNNEST, and even attempting to bypass the filter when the array is empty, but nothing seems to work.

Here’s a bit more context:

  • I’m working with multiselect dropdowns, and I need to filter a dataset based on the selected values. However, when the selection is empty, BigQuery throws the error mentioned above.
  • I’ve tried transforming the arrays and using JavaScript to conditionally build the query, but the error persists.
  • I understand that BigQuery requires a type for empty arrays, but I’m not sure how to handle this within the Retool environment.

Has anyone else encountered this issue? How did you manage to resolve it? Any tips, suggestions, or solutions would be greatly appreciated!

Thanks in advance for your help!

image
image706×402 80 KB

2

maybe try:

{{ {RT$BQ_TYPE: 'ARRAY', value: select1.value} }} = ARRAY<STRING>[]

or using CAST(NULL as ARRAY<STRING>) on the value being read?

i think the problem is in how the value NULL is stored for an array of any type. when BigQuery stores something w a NULL value it lioks like it actually stores []. so when you read the value your expecting to see NULL, which is why you've rightfully tried every permutation of comparing to NULLyou could think of, but instead you're actually reading ARRAY<type_name>[]... you can change the WHERE up all you want and end up seeing the same error 100% of the time, cause its the SELECT * being evaluated (the * breaks down to table.column_name w a value of ARRAY<>[] but the <> part of the array requires a type name to be valid) and throwing the error before the WHERE is even known :wink:

there's this SO post w some examples also