Bigquery:附加到嵌套记录 [英] Bigquery: Append to a nested record

问题描述

我目前正在检查Bigquery，我想知道是否有可能将新数据添加到嵌套表中.

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"
            }
        ]
    }
]

然后我输入联系人John Doe的电话号码.

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.

推荐答案

将数据插入BigQuery时，粒度是行的级别，而不是行中包含的数组元素.您可能想使用这样的查询，在其中更新相关行并追加到数组:

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"

这篇关于Bigquery:附加到嵌套记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！