Insert into `dataset`.`table` (`field`) values ({}) - Value has type STRUCT<> which cannot be inserted into column field, which has type JSON

Workflows
,
1

Hi everyone,

I am trying to push some JSON data into our BigQuery instance through a Workflow (Gets triggered through a webhook). I use the GUI SQL editor, and map the Key Value pairs for a field that has some JSON data in it. On BigQuery, the corresponding field is also set as JSON. But unfortunately, the data auto-sent by Retool tries to capture the JSON structure with a STRUCT. Since that JSON is dynamic, we do NOT want to use a STRUCT on BigQuery, but rather we want to keep it as a JSON blob.

My question is, is it somehow possible on the Workflow UI to force Retool to send such values as JSON rather than a STRUCT? I've tried {{JSON.stringify(field.value)}} instead of {{field.value}} while mapping the value to the field, but that did not do the trick.

I do NOT want to manually construct the JSON '{}' string myself, and am hoping to find a Retool-sanctioned solution to the problem.

Thanks,

Cansin

2

I was able to figure this one out by manually expanding the data and then using TO_JSON({{value}}) on the SQL editor.

I am now unable to have a dynamic table name inserted into the SQL editor. When I try to use something like {{``some_dataset_${retoolContext.configVars.ENV}\.some_table``}}, I end up with a Syntax error: Unexpected "?" at [2:4].

3

Hi @cansin_at_ea, welcome to the forum! :wave:

Happy to hear you were able to figure the first part out!

From looking at the code you shared:

{{``some_dataset_${retoolContext.configVars.ENV}\.some_table``}}

It is hard to tell without seeing the entire query or your config vars but the "\" after ".ENV}" is suspicious. :face_with_monocle: