Describe the issue/error/question
I need to insert rows to a bigquery table with data from an external API.
Basically, the table on bigquery is composed by:
- id (INTEGER)
- reference_id (INTEGER)
- details (JSON)
The problem is I can’t find a way to insert data to a column on the bigquery table which type is JSON.
Bigquery just returns a message that the format is invalid. Searching about this topic, someone told me to try to transform the JSON (details) to string and then insert on bigquery, but I couldn’t make it work.
Follow the example.
Node 1: Data in JSON format.
[
{
“id”:692977012,
“numero”:26,
“details”:{
“numero_ecommerce”:8619,
“data_pedido”:“08/01/2022”,
"data_prevista:“08/01/2022”
}
}
]
Node 2: Transform data with SET node
I use SET node type to organize the data from the last node to the bigquery column model.
n8n → bigquery
- id → id
- numero → reference_id
- details → details
The results on bigquery would be:
id: 692977012
reference_id: 26
details: [{“numero_ecommerce”:8619,“data_pedido”:“08/01/2022”,"data_prevista:“08/01/2022”}]