Mapping null values to a Repeated String Field in BigQuery

I am updating an existing row in BigQuery that is Type: String, Mode: Repeated. I was getting the error:

"Parameter type must be provided for empty array values."

I found the solution to use:

{{ {RT$BQ_TYPE: 'STRING', value: null} }}

Now I am getting the error:

Screenshot 2024-02-06 at 5.32.33 PM

Screenshot 2024-02-06 at 5.12.40 PM

What is my best option to resolve this?

@victoria I saw you were working on the original thread, do you have any insights here?

so the type of contactPointsNew is an Array?

Maybe try ContactPointsNew = UNNEST([]) if you don't want any value/set as empty list?

@Haseeb1399 unnest is not a recognized function, unfortunately.

Unnest is a Big Query function. Are you passing UNNEST to the value? (In javascript)?

Screenshot 2024-02-06 at 6.30.34 PM

Try ContactPointsNew = UNNEST([]) instead of ContactPointsNew=UNNEST({{ .... }})

My assumption here is that your trying to set ContactPointsNew to be an empty Array

It is already being pushed through as an empty array, that is the issue. Bigquery is asking for empty arrays to have parameter type pushed through as well, which I did, but the parameter type is not being accepted since the parameter is repeated and not nullable.

This is only happening when I have nothing selected in the element in question. How it is set up now works when a value is selected.

:wave: Not seeing any known bugs related to this on the Retool side. One thing that folks have found useful is hardcoding the query instead of using double curly brackets {{ }} to confirm that the syntax works ok without those.

Beyond that, in terms of possible next steps I would recommend looking into whether BigQuery's public docs or resources have known workarounds or solutions :thinking: