CODEWITHSUNDEEP
gogoogle-bigquery
Reza Shahbazi
Reza Shahbazi

Reputation: 451

Insert data to nested fields of a table at Google BigQuery

I have a table in Cloud BigQuery, but the service.Tabledata.InsertAll call does insert data to the nested fields.

// works
 jsonRow["name"] = bigquery.JsonValue("Name")

// doesn't work
jsonRow["geo_location.City.Names.en"] = bigquery.JsonValue("Irvine")

rows[index] = new(bigquery.TableDataInsertAllRequestRows)
rows[index].Json = jsonRow
insertRequest := &bigquery.TableDataInsertAllRequest{Rows: rows}
insertRequest.IgnoreUnknownValues = true

call := service.Tabledata.InsertAll(project, dataset, "analytics_events", insertRequest)

if res, err := call.Do(); err!=nil{
   Log.Fatal("Unable to Insert to BigQuery ", err)
   return err
}

Upvotes: 1

Views: 1252

Answers (2)

Sean Chen
Sean Chen

Reputation: 671

You actually need to construct an object structure that matches the structure of your schema.

The confusion here is that the line:

jsonRow["geo_location.City.Names.en"] = bigquery.JsonValue("Irvine")

Doesn't create an object structure that you're expecting. The json object you've made actually looks like this:

{
  "geo_location.City.Names.en": "Irvine"
}

Whereas you want something that looks like:

{
  "geo_location": {
    "City": {
      "Names": {
        "en": "Irvine"
      }
    }
  }
}

So your code should look something like:

// Probably not valid code. Just guessing.
jsonRow["geo_location"] = bigquery.JsonObject()
jsonRow["geo_location"]["City"] = bigquery.JsonObject()
jsonRow["geo_location"]["City"]["Names"] = bigquery.JsonObject()
jsonRow["geo_location"]["City"]["Names"]["en"] = bigquery.JsonValue("Irvine")

Hope that helps.

Upvotes: 1

Jeremy Condit
Jeremy Condit

Reputation: 7046

You need to construct a nested object client-side, instead of using dotted notation inside the key for your jsonRow.

Upvotes: 0

Related Questions