Getting error while inserting array of json to bigQuery

Question

Error while reading data, error message: Failed to parse JSON: No object found when new array is started.; BeginArray returned false

I have created below sample json data. which is array of json. And each json object is in new line.

It works, when I load only one json object without keeping inside array.

JSON body -

[
  { "item_name": "dfkhjf", "gtin": "123456", "brand": "Om-Publication","category_name": "books", "country_code": "IN", "marktet_place": "india", "price": 2239, "sellerId": 234, "create_time": "2017-07-19T16:00:46.000Z" },
  { "item_name": "toy-gun", "gtin": "1234234445", "brand": "Toy", "category_name": "toy", "country_code": "IN", "marktet_place": "flipMe", "price": 2239, "sellerId": 234, "create_time": "2017-08-19T16:00:46.000Z" },
  { "item_name": "Drone", "gtin": "12342356456", "brand": "Drone-XX", "category_name": "drone", "country_code": "IN", "marktet_place": "drone-maker", "price": 2239, "sellerId": 234, "create_time": "2017-09-19T16:00:46.000Z" }
]

Can you explain how you do the insert command and provide an example — Tamir Klein
– Tamir Klein, Commented Jan 3, 2019 at 11:17
I have uploaded this data in a file say -mydata.json in GCS bucket. Then from the google console webUI, I am creating table keeping schema auto-detect true. and uploading this file from GCS. — Aditya
– Aditya, Commented Jan 3, 2019 at 11:25
If you want the get 3 lines in BigQuery you can remove the [] from your JSON and it should work — Tamir Klein
– Tamir Klein, Commented Jan 3, 2019 at 11:27

dsesto · Accepted Answer · 2019-01-04 09:25:23Z

As explained in the documentation for loading JSON data stored in GCS to BigQuery, the JSON data must be in Newline Delimited JSON format, in which each line is a valid independent JSON value, therefore instead of (1), you should use (2)):

(1)

[
  { "item_name": "dfkhjf", "gtin": "123456", "brand": "Om-Publication","category_name": "books", "country_code": "IN", "marktet_place": "india", "price": 2239, "sellerId": 234, "create_time": "2017-07-19T16:00:46.000Z" },
  { "item_name": "toy-gun", "gtin": "1234234445", "brand": "Toy", "category_name": "toy", "country_code": "IN", "marktet_place": "flipMe", "price": 2239, "sellerId": 234, "create_time": "2017-08-19T16:00:46.000Z" },
  { "item_name": "Drone", "gtin": "12342356456", "brand": "Drone-XX", "category_name": "drone", "country_code": "IN", "marktet_place": "drone-maker", "price": 2239, "sellerId": 234, "create_time": "2017-09-19T16:00:46.000Z" }
]

(2)

{ "item_name": "dfkhjf", "gtin": "123456", "brand": "Om-Publication","category_name": "books", "country_code": "IN", "marktet_place": "india", "price": 2239, "sellerId": 234, "create_time": "2017-07-19T16:00:46.000Z" }
{ "item_name": "toy-gun", "gtin": "1234234445", "brand": "Toy", "category_name": "toy", "country_code": "IN", "marktet_place": "flipMe", "price": 2239, "sellerId": 234, "create_time": "2017-08-19T16:00:46.000Z" }
{ "item_name": "Drone", "gtin": "12342356456", "brand": "Drone-XX", "category_name": "drone", "country_code": "IN", "marktet_place": "drone-maker", "price": 2239, "sellerId": 234, "create_time": "2017-09-19T16:00:46.000Z" }

UPDATE:

Here a step-by-step guide in order to show how this works:

Create a JSON file (file.json in my case) with the content I shared (make sure to remove the array brackets [] and also the commas , at the end of each line:

$ cat file.json
{ "item_name": "dfkhjf", "gtin": "123456", "brand": "Om-Publication","category_name": "books", "country_code": "IN", "marktet_place": "india", "price": 2239, "sellerId": 234, "create_time": "2017-07-19T16:00:46.000Z" }
{ "item_name": "toy-gun", "gtin": "1234234445", "brand": "Toy", "category_name": "toy", "country_code": "IN", "marktet_place": "flipMe", "price": 2239, "sellerId": 234, "create_time": "2017-08-19T16:00:46.000Z" }
{ "item_name": "Drone", "gtin": "12342356456", "brand": "Drone-XX", "category_name": "drone", "country_code": "IN", "marktet_place": "drone-maker", "price": 2239, "sellerId": 234, "create_time": "2017-09-19T16:00:46.000Z" }

Load the file to BQ, running a command like below:

$ bq load --autodetect --source_format=NEWLINE_DELIMITED_JSON dataset.table file.json                                   
Upload complete.
Waiting on bqjob_XXXXXXXXXXX ... (1s) Current status: DONE

Now query the table in order to check that the content was uploaded correctly:

$ bq query --use_legacy_sql=false "SELECT * FROM dataset.table"
Waiting on bqjob_r3ef14ac0d0a6c856_000001681819e9fc_1 ... (0s) Current status: DONE
+----------+-------+---------------------+---------------+----------------+--------------+---------------+-------------+-----------+
| sellerId | price |     create_time     | marktet_place |     brand      | country_code | category_name |    gtin     | item_name |
+----------+-------+---------------------+---------------+----------------+--------------+---------------+-------------+-----------+
|      234 |  2239 | 2017-07-19 16:00:46 | india         | Om-Publication | IN           | books         |      123456 | dfkhjf    |
|      234 |  2239 | 2017-08-19 16:00:46 | flipMe        | Toy            | IN           | toy           |  1234234445 | toy-gun   |
|      234 |  2239 | 2017-09-19 16:00:46 | drone-maker   | Drone-XX       | IN           | drone         | 12342356456 | Drone     |
+----------+-------+---------------------+---------------+----------------+--------------+---------------+-------------+-----------+

@jyoti I have updated my answer, showing the step-by-step process to make the load work. If you still encounter issues with this, please be as specific as possible with the issues / errors that you are seeing.
Should OP's load job have worked if the json object had that exact same content but in a single line? I understand you'd have to UNNEST it once it is in bq.

Collectives™ on Stack Overflow

Getting error while inserting array of json to bigQuery

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related