Create table in bigquery fails when the JSON key contains hyphen

Question

I have a JSON data from Firebase Backup. Data generated is such that every key is preceded by a hyphen.

Sample data is as follows:

"-GuGCJDEprMKczAMDUj8":{"deviceId":"399a649c6cee6209","dow":"Thursday","downloadFlag":"N","event":"streamStart","halfHourFull":"18h1","liveFlag":"Y","localDate":"2009-01-01","localHalfHour":1,"minutesSinceMidnight":1080,"quarterHourFull":"18q1","stationName":"hit 105","streamListenMethod":"Headphones","timestampLocal":"2009-01-01T18:00:33.679+10:00","timestampUTC":"2009-01-01T08:00:33.679Z"}

When we are trying to load that data into Bigquery then we are encountered with the below mentioned error:

Fields must contain only letters, numbers, and underscores, start with a letter or underscore, and be at most 128 characters long.

Is this a bigquery limitation? If yes, then what's the proposed solution here.

Any help/suggestion is much appreciated.

Elliott Brossard Elliott Brossard · Accepted Answer · 2018-08-30T15:43:52

Is this a bigquery limitation? If yes, then what's the proposed solution here.

You need to use different field names instead. One option is to load the data into a single STRING column, e.g. by using 'CSV' for the format with a field delimiter of '|' (or any other character that doesn't appear in your data). Then you can use the JSON_EXTRACT_SCALAR function to extract fields from the JSON, e.g.:

CREATE TABLE dataset.table AS
SELECT
  JSON_EXTRACT_SCALAR(json_string, '$.-GuGCJDEprMKczAMDUj8.deviceId') AS deviceId,
  JSON_EXTRACT_SCALAR(json_string, '$.-GuGCJDEprMKczAMDUj8.dow') AS dow,
  JSON_EXTRACT_SCALAR(json_string, '$.-GuGCJDEprMKczAMDUj8.downloadFlag') AS downloadFlag,
...
FROM dataset.single_column_table

Create table in bigquery fails when the JSON key contains hyphen

1 Answers