jsonEncode to Big Query

[rwild_node]

Im having some difficulty pushing data to Big Query after some assistance ( I’m sure its a basic fix).

• Sending 3 things to a big query data table namely SerNo, timestamp and a json string.
• I am using the jsonEncode node to encode the payload to {{data.body2}}
• I then insert the result into the big query node as follows:
  

I am receiving the following error from google: “parse error at line 1, column 2: syntax error while parsing object key - invalid literal; last read: ‘{&’; expected string literal”

Not sure what I am doing wrong, if instead of using the handlebar insert {{data.body2}} in the big query table data json template, I just paste the result of {{data.body2}} direct from the debug console it seems to work fine. An example here of the table data json template:

result in google:

So it seems by using the {{data.body2}} in the json template it is messing with the json structure?

[Sebastian_Turner]

Hi @rwild_node, can you post the entirety of a successful and an unsuccessful Debug log here? Or, if you’d prefer, you can DM it to me.

[Dylan_Schuster]

I think this is the problem …

data.body2 is stored on your payload as a stringified JSON object - the string {"foo": "bar"}.

You are then trying to construct a JSON template where you are placing that string value inside of double quotes -

{
  "myValue": "{{data.body2}}"
}

When this renders, it becomes …

{
  "myValue": "{"foo": "bar"}"
}

… this is not valid JSON.

The reason it is working when you copy the value from the debug log is because we are escaping those quotes - stringifying the already stringified JSON …

{
  "myValue": "{\"foo\": \"bar\"}"
}

… which is valid JSON.

---

So what you want to do instead is this …

{
  "myValue": {{jsonEncode data.body2}}
}

The {{jsonEncode}} helper will properly escape the values in the JSON string for you. Note the lack of quotes around the helper; since the end value is a string, the helper will take care of adding those for you.

[rwild_node]

That was it, thanks @Dylan_Schuster

[rwild_node]

@Dylan_Schuster looks like I am running into a problem on the Query side also with the JSON field type in big query. Not sure if this is underlying in the GCP node or if it is my poor syntax. Example of query on gcp platform:

Current workflow GCP node JSON template config to mirror above query:

{
“query”: “SELECT* FROM [knowsy-warm-store.knowsy_production_warm.data_record_oem] LIMIT 100”
}

Resulting error:

Detail: “Querying tables with INTERVAL or JSON type is not supported in Legacy SQL: 640499537116:knowsy_production_warm.data_record_oem.”

[Dylan_Schuster]

I spent a while looking into this and verified the issue. The underlying library we use to make requests to BigQuery defaults to using Legacy SQL for the Jobs: Query action, and as the error message says, that is not allowed when querying a table that has JSON field types.

I’m filing a feature request to allow for choosing Legacy SQL or Google SQL as the query syntax. I’m hoping that makes our May release. In the meantime, you’ll have to work around this by either using a GCP: Function Node to invoke a function on your infrastructure that then returns results from this table, or use an HTTP Node to make the request to the jobs.query method.

Thanks for bringing this to our attention. I will let you know when we expose that new option in the BigQuery Node.

[Dylan_Schuster]

@rwild_node our developers looked into this and figured out that you can resolve this issue right now without any necessary platform changes.

You need to add "useLegacySql": false to your query request, like so:

{
  "query": "SELECT* FROM [knowsy-warm-store.knowsy_production_warm.data_record_oem] LIMIT 100",
  "useLegacySql": false
}

I tested this on my end and was able to make a query to a table with a JSON-type column.

[rwild_node]

Thanks for the follow up @Dylan_Schuster , appreciate the solution here!