CODEWITHSUNDEEP
google-bigquery
Dave Chambers
Dave Chambers

Reputation: 2563

How can I insert data into this structure in BigQuery?

In BigQuery, I want to insert some data into this very simple data structure:

Field             Type     Mode

id                STRING   NULLABLE
policies          RECORD   REPEATED
 s                RECORD   NULLABLE
  something       STRING   NULLABLE
  riskTypes       RECORD   REPEATED
   code           STRING   NULLABLE

In the light of my previous question I would expect the syntax to be as follows:

UPDATE `tablename` SET policies = ARRAY_CONCAT(
policies, [
    struct<s  struct<something STRING, riskTypes ARRAY<struct<code STRING>>>
    ("example something", [("example description")])
]
) 
WHERE id = 'Moose';

But this gives an error:

Unexpected "[" (before the "example description")

Upvotes: 0

Views: 127

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Below should work

update `tablename` set policies = policies ||  [
  struct<s struct<something string, riskTypes array<struct<code string>> >>
    (struct('example something' as something, [struct('example description 1' as code), struct('example description 2')]))  
]
where id = 'Moose'

Upvotes: 1

Related Questions