Issue with JSON object nested in an Array when inserting in BigQuery

Question

I have a hard time understanding what is going wrong in the nesting I am doing. I want to insert a row in Google Big Query using their Nodejs client. To do so, I need to push an json object nested in an array as followed:

[ { timestamp: '1533564208', device_id: '2nd_test', temperature: '20.0' } ]

When hard writing this in my code, I can add a row in Big Query with no issue.

const rows = [
{ timestamp: '1533564208', device_id: '2nd_test', temperature: '20.0' }
];

bigquery
.dataset(datasetId)
.table(tableId)
.insert(rows)

Now what I actually want to do is to insert the payload I get from pubsub in Big Query and this is where I face issues. The pubsub data is managed as followed:

var payload = Buffer.from(pubsubMessage.data, 'base64').toString();
console.log(payload);
// [ { timestamp: '1533564208', device_id: '2nd_test', temperature: '20.0' } ] 

Based on the log it should work - it is a JSON object nested in an array, but when it calls the Big Query API it hits an Invalid value error:

error: ERROR: { ApiError: Invalid value at 'rows[0].json' (type.googleapis.com/google.protobuf.Struct), "[ { timestamp: '1533564208', device_id: '2nd_test', temperature: '20.0' } ]"
at Object.parseHttpRespBody (/Users/marion/google_iot_core_test/google_function/timeseriesBigQuery/node_modules/@google-cloud/bigquery/node_modules/@google-cloud/common/src/util.js:193:30)
at Object.handleResp (/Users/marion/google_iot_core_test/google_function/timeseriesBigQuery/node_modules/@google-cloud/bigquery/node_modules/@google-cloud/common/src/util.js:131:18)
at /Users/marion/google_iot_core_test/google_function/timeseriesBigQuery/node_modules/@google-cloud/bigquery/node_modules/@google-cloud/common/src/util.js:496:12
at Request.onResponse [as _callback] (/Users/marion/google_iot_core_test/google_function/timeseriesBigQuery/node_modules/retry-request/index.js:198:7)
at Request.self.callback (/Users/marion/google_iot_core_test/google_function/timeseriesBigQuery/node_modules/request/request.js:185:22)
at emitTwo (events.js:126:13)
at Request.emit (events.js:214:7)
at Request.<anonymous> (/Users/marion/google_iot_core_test/google_function/timeseriesBigQuery/node_modules/request/request.js:1161:10)
at emitOne (events.js:116:13)
at Request.emit (events.js:211:7)
code: 400,
errors: 
 [ { message: 'Invalid value at \'rows[0].json\' (type.googleapis.com/google.protobuf.Struct), "[ { timestamp: \'1533564208\', device_id: \'2nd_test\', temperature: \'20.0\' } ]"',
   domain: 'global',
   reason: 'badRequest' } ],
response: undefined,
message: 'Invalid value at \'rows[0].json\' (type.googleapis.com/google.protobuf.Struct), "[ { timestamp: \'1533564208\', device_id: \'2nd_test\', temperature: \'20.0\' } ]"' }

EDIT: I digged into what value would give me rows[0] and for some unknown reason it seems to consider it as a string and just returns the following

console.log(rows[0]);
// [

instead of

// { timestamp: '1533564208', device_id: '2nd_test', temperature: '20.0' }

But when I try to JSON.parse it it sends out an error.

Any idea on what the issue is?

Thanks!

Answer 1

As mentionned in the comments the issue was that I was not sending a valid JSON string. What was hard to understand was that hardcoded in the code the 2 below versions would give me the same result and work fine.

{ timestamp: "1533564208", device_id: "2nd_test", temperature: "20.0" } 

and

'{"timestamp":"1533564208","device_id":"2nd_test","temperature":"20.0"}'

But when using the PubSub Gcloud and the Buffer function, I had to make sure to pass in

'{"timestamp":"1533564208","device_id":"2nd_test","temperature":"20.0"}' 

and not

{ timestamp: "1533564208", device_id: "2nd_test", temperature: "20.0" } 

otherwise it would not consider it as valid JSON.