Updating bigQuery array with dynamic values

Queries and Resources
, ,
1

Hi! I have a problem.
I want to update bigQuery data through retool, and I need to handle array of object with possible null values.
I tried using transformer to adjust the types and make sure everything is ok, but no matter how i address it - i still get the error "parameter type must be provided for null values."
please let me know how to solve it.
i tried both GUI and SQL and nothing works,
here is an example of my code from the transformer while using GUI with using Bulk insert records : (general names)

const data = {{ formData }}
const transformedData = data.map(item => ({
  keyName: item.keyName !== null ? item.keyName : {{{ RT$BQ_TYPE: 'FLOAT64', value: null }}},
  keyName2: item.keyName2,
  keyName3: item.keyName3 !== null ? item.keyName3 : {{{ RT$BQ_TYPE: 'STRING', value: null }}},
  keyName4: item.keyName4,
  keyName5: item.keyName5 !== null ? item.keyName5 :{{ { RT$BQ_TYPE: 'STRING', value: null }}}
}));

return transformedData;

Self-hosted Retool 2.91

1 Like
2

Hello @Shani_Mekiten_Shnayer!

Apologies for the issue, that is a weird error as it seems that you are properly handling null values and converting them into values that can be read and uploaded to bigQuery.

I can try to reproduce this and see if the issue is coming from bigQuery or if Retool is throwing this error as a 'guard rail' to prevent sending null values on accident.

Then I can narrow down on if the mapping logic is working as intended or if there is another way to ensure the "parameter type must be provided for null values" is not occurring.

Are you able to use bigQuery to send test values that are non-null to make sure the query/resource are set up and working fine?

If you can share any screenshots of the error and the result of the transofrmedData to ensure that the items are being converted properly and before the query prepares and dispatches the data that would be very helpful!