Push to big query, need help with payload structuring

Help
1

Hi there

I am still learning how to work and construct payloads.

can you please assist me with this use-case?.

image
image.png287×598 9.2 KB

and here is the payload of the device state:

{
“time”: “2018-01-10T07:20:52.592Z”,
“data”: {
“Dn”: 0,
“Dm”: 0,
“Dx”: 315,
“Sn”: 0,
“Sm”: 0,
“Sx”: 0.4,
“Ta”: 13.8,
“Ua”: 83.5,
“Pa”: 1004.5,
“Rc”: 0.4,
“Sr”: 82.1,
“result”: {
“error”: {
“code”: 400,
“errors”: [
{
“message”: “No rows present in the request.”,
“domain”: “global”,
“reason”: “invalid”
}
]
}
}
},
“applicationId”: “xxx”,
“triggerId”: “xxx”,
“triggerType”: “deviceId”,
“relayId”: “xx”,
“relayType”: “device”,
“deviceTags”: {
“model”: [
“UTX-3117”
],
“location”: [
“Yair House”
],
“FW”: [
“1.0.0”
],
“DeviceRecipe”: [
“Advantech Gateway”
]
},
“deviceName”: “Yair Gateway”,
“flowVersion”: “develop”,
“flowId”: “xxx”,
“flowName”: “Save to google”,
“applicationName”: “AutoPivot”,
“globals”: {}
}

from which I’d like to structure a JSON that looks like:

{
“deviceName”: “Yair Gateway”,
“deviceId”: “xxxxxxx”,
“model”: “UTX3117”,
“TS”: “2018-01-10T07:20:52.592Z”,
“data”: {
“Dn”: 0,
“Dm”: 0,
“Dx”: 315,
“Sn”: 0,
“Sm”: 0,
“Sx”: 0.4,
“Ta”: 13.8,
“Ua”: 83.5,
“Pa”: 1004.5,
“Rc”: 0.4,
“Sr”: 82.1
}
}

Can I do it in the Table Data JSON Template in the bigquery node, or do I need to use the function node?

I am sure once I see this example, i’ll be able to go pretty far myself :wink:

2

You can use the JSON template in the BigQuery Node to achieve what you’re after. Here’s a limited example to get you going; filling in the rest should be easy -

{
	"deviceName": "{{deviceName}}",
	"data": {
		"Dn": {{data.Dn}}
	}
}

The important thing to note here is that anything you want to be passed as a string should still be put in double-quotes, as the Handlebars templates will render out the value literally and not typed. For your data, assuming you want to pass those values as numbers, you would not put them in quotes.

Here’s some documentation on JSON templates, as well as a number of helpers that may be of use to you as you construct these payloads.

Let us know if you have any more questions!

1 Like
3

Is there a way to work with the array and include all the results under “data”:{ } ? not that i mind to do the manual copy/paste work but i am trying to learn to be as effective as possible.

4

ok here is how i did it, in your example, you left out the “” so it took me a moment to figure it out:


{
	"rows": [{
		"json": {
			"deviceName": "{{data.foo.name}}",
			"deviceId": "{{data.foo.deviceId}}",
			"model": "UTX3117",
			"TS": "{{format time 'x'}}",
			"Dn": "{{data.Dn}}",
			"Dm": "{{data.Dm}}",
			"Dx": "{{data.Dx}}",
			"Sn": "{{data.Sn}}",
			"Sm": "{{data.Sm}}",
			"Sx": "{{data.Sx}}",
			"Ta": "{{data.Ta}}",
			"Ua": "{{data.Ua}}",
			"Pa": "{{data.Pa}}",
			"Rc": "{{data.Rc}}",
			"Sr": "{{data.Sr}}"
		}
	}]
}
1 Like
5

Ok, so the timestamp is looking a bit funky in google bigquery,

this is what i am seeing there: 50005-12-28 08:12:05.000 UTC

for: Sat Jan 13 2018 20:50:53 GMT+0200

after passing it like this: “TS”: “{{format time ‘x’}}”,

am i doing it wrong?

6

It looks like BigQuery expects the timestamp in seconds, and you’re sending it up as milliseconds, which is why your data is 48,000 years into the future.

You can change the format to {{format time 'X'}}, which will print the value in seconds instead, but you’ll lose your millisecond precision by doing so. The other option is to divide the number by 1,000 (easiest way to do that is a Math Node) and put that value back on your payload overtop the original timestamp.

Also, in regards to this post and your previous ones, you are sending your data up as strings by including the double-quotes around the Handlebars templates. BigQuery may be casting these as numbers for you, but if not, you should remove those quotes to ensure the data is in the correct format.

For example, if the value at the payload path of data.value is 42, this JSON template …

{ "data": "{{data.value}}" }

would resolve to …

{ "data": "42" }

… whereas this JSON template …

{ "data": {{data.value}} }

would resolve to …

{ "data": 42 }

Finally, I’m a little unclear on what you were hoping to do with the Array Node. If you can give me an example payload and an example output, I may be able to point you in the right direction.