CODEWITHSUNDEEP
jsongoogle-cloud-platformgoogle-bigquery
Guillaume
Guillaume

Reputation: 178

Is there a way to generate a BigQuery table based on JSON data?

Let's say we have a simple JSON array as follows:

[
  {
    "id": "0001",
    "customer": {
      "name": "John",
      "age": 30,
      "cars": [
        "Ford",
        "BMW",
        "Fiat"
      ]
    }
  },
  {
    "id": "0002",
    "customer": {
      "name": "Mary",
      "age": 22,
      "cars": [
        "Ford",
        "Fiat"
      ]
    }
  }
]

Is there a way to take that JSON array to automatically create a BigQuery table, infer its schema and populate it with the JSON values? A native solution would be nice but it can be something else too. The resulting schema should look like this:

enter image description here

And, ideally, we should also be able to see in the table the data extracted from the JSON array:

enter image description here

I don't see any technical impediment to that kind of requirement but I haven't found any off-the-shelf solution whereas I know there's a need for it around me.

Upvotes: 0

Views: 1384

Answers (1)

Sakshi Gatyan
Sakshi Gatyan

Reputation: 2126

While creating/loading table using JSON data file, JSON data must be newline delimated ie. each object must be in a separate line in the file. (As mentioned by @guillaume)

BQ doesn't support loading direct JSON array to create records and hence it will result in error while parsing the JSON(array) data file during the load job.

Hence, the working JSONL file in your case would be:

{ "id": "0001", "customer": { "name": "John", "age": 30, "cars": [ "Ford","BMW","Fiat"]}}
{ "id": "0002", "customer": { "name": "Mary", "age": 22, "cars": [ "Ford", "Fiat"]}}

Detailed information on Loading JSON data can be found in this Google documentation.

Upvotes: 3

Related Questions