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!

I sort of figured this out so I tried several things: having the payload being only a JSOn object and then pushing it in the array, adding JSON.parse. Didn't work neither. — fro
– fro, Commented Aug 21, 2018 at 13:21
What is weird is that it says invalid value at 'rows[0].json' though the value specified after seems totally valid: "[ { timestamp: '1533564208', device_id: '2nd_test', temperature: '20.0' } ]" — fro
– fro, Commented Aug 21, 2018 at 16:27
I think it is invalid json, if it is within a string, unless the keys as well as the values are in quoted? — Chris Cousins
– Chris Cousins, Commented Aug 21, 2018 at 16:45
You are def right! I am trying to understand why though... I posted another question: stackoverflow.com/questions/51953504/… — fro
– fro, Commented Aug 21, 2018 at 16:55

fro · Accepted Answer · 2018-08-22 09:32:47Z

5

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.

answered Aug 22, 2018 at 9:32

fro

4875 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Williamson Over a year ago

Could you provide the actual answer, if you happen to remember it? As I understand, you are first performing toString, so it should have been receiving a string. What was the actual problem? You claim here that you needed to pass a string, but the original question looks like you already were.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related