Bigquery: Append to a nested record

Question

I'm currently checking out Bigquery, and I want to know if it's possible to add new data to a nested table.

For example, if I have a table like this:

[
    {
        "name": "name",
        "type": "STRING"
    },
    {
        "name": "phone",
        "type": "RECORD",
        "mode": "REPEATED",
        "fields": [
            {
                "name": "number",
                "type": "STRING"
            },
            {
                "name": "type",
                "type": "STRING"
            }
        ]
    }
]

And then I insert a phone number for the contact John Doe.

INSERT into socialdata.phones_examples (name, phone) VALUES("Jonh Doe", [("555555", "Home")]);

Is there an option to later add another number to the contact ? To get something like this:

I know I can update the whole field, but I want to know if there is way to append to the nested table new values.

Answer 1

When you insert data into BigQuery, the granularity is the level of rows, not elements of the arrays contained within rows. You would want to use a query like this, where you update the relevant row and append to the array:

UPDATE socialdata.phones_examples
SET phone = ARRAY_CONCAT(phone, [("555555", "Home")])
WHERE name = "Jonh Doe"

Answer 2

if you need to update multiple records for some users - you can use below

#standardSQL
UPDATE `socialdata.phones_examples` t
SET phone = ARRAY_CONCAT(phone, [new_phone]) 
FROM (
  SELECT 'John Doe' name, STRUCT<number STRING, type STRING>('123-456-7892', 'work') new_phone UNION ALL
  SELECT 'Abc Xyz' , STRUCT('123-456-7893', 'work') new_phone 
) u
WHERE t.name = u.name   

or if those updates are available in some table (for example socialdata.phones_updates):

#standardSQL
UPDATE `socialdata.phones_examples` t
SET phone = ARRAY_CONCAT(phone, [new_phone]) 
FROM `socialdata.phones_updates` u
WHERE t.name = u.name