如何修复BigQuery嵌套REPEATED RECORD表的UPDATE查询语法错误

你遇到的报错核心是两个待拼接的数组元素的STRUCT类型不完全匹配，BigQuery要求ARRAY拼接时元素的STRUCT字段顺序、字段名、字段类型必须完全一致，你当前构造的结构体中casedata子结构的dateCreated、dateLastModified字段被声明为STRING，和原表定义的TIMESTAMP类型不匹配，这是问题根源。

1. 本次UPDATE操作的正确语法 #

首先修正类型不匹配的问题，将casedata中对应字段的类型改为TIMESTAMP，同时确保所有字段顺序、类型和原表完全一致，推荐用ARRAY_CONCAT代替||做数组拼接，可读性更强，修正后的SQL如下：

UPDATE `table` 
SET things = ARRAY_CONCAT(things,  [
        STRUCT<key STRING, lifeid STRING, monthly STRING, thingtype STRING, thingvalue INTEGER, 
            provider STRUCT<premium STRING, provider STRING, rank STRING>, 
            randomcompany STRUCT< 
                someBool BOOLEAN,
                anotherBool STRUCT<code STRING, description STRING, listName STRING>,
                casedata STRUCT<caseid STRING, dateCreated TIMESTAMP, dateLastModified TIMESTAMP, interviewTerminated BOOLEAN, status STRING>,
                hasAttachments BOOLEAN,
                hasX BOOLEAN,
                lifeId STRING,
                lifeIdentifier STRING,
                status STRUCT<code STRING, description STRING, listName STRING>,
                mibComplete BOOLEAN,
                thingid STRING,
                riskTypes ARRAY<STRUCT<code STRING, description STRING, listname STRING>>
            >, 
            termlength STRING, timestamp TIMESTAMP>
        ('example key', 'example lifeid', 'example monthly', 'example thingtype', 4, 
            STRUCT('example premium' as premium, 'example provider' as provider, 'example rank' as rank), 
                STRUCT(
                    true,
                    STRUCT('a code' as code, 'a description' as description, 'a listName' as listName),
                    STRUCT('6752' as caseid, CAST(NULL AS TIMESTAMP) as dateCreated, CAST(NULL AS TIMESTAMP) as dateLastModified, false as interviewTerminated, 'OPEN' as status),
                    false,
                    false,
                    'LV25APL37',
                    'LV25APL37',
                    STRUCT('a code' as code, 'a description' as description, 'a listName' as listName),
                    false,
                    'ST11006752',
                    [STRUCT('a code' as code, 'a description' as description, 'a listName' as listname)]
                ), 
                'example termlength', CAST(NULL AS TIMESTAMP)
        )
    ])
WHERE id = '7PHh1dN0HdVGCAzIDIVkPWQ4GjI3';

这里额外给randomcompany外层加了显式的STRUCT声明，同时把NULL值都显式转成了对应类型，避免类型推导错误。

2. 查看完整错误信息的方法 #

• Web UI方式：打开BigQuery控制台的「查询历史」页面，找到对应失败的作业，点击进入详情页，展开「错误详情」模块，可以看到完整的类型不匹配提示，会明确列出左右两个STRUCT的字段差异点。
• bq命令行方式：执行bq --format=json show -j [作业ID]，输出结果的errors[0].message字段会包含完整的错误信息，比prettyjson格式展示的内容更全。
• 预校验方式：单独执行SELECT语句验证构造的数组类型是否匹配，比如跑SELECT things FROM \table` WHERE id = '7PHh1dN0HdVGCAzIDIVkPWQ4GjI3' UNION ALL SELECT [你构造的结构体数组]`，SELECT语句的报错会比UPDATE更详细，不需要扫描全表，调试效率更高。

3. 显式声明STRUCT的场景和原因 #

需要显式写STRUCT的场景： #

• 构造的STRUCT字段顺序、字段名和BigQuery默认推导的结果不一致，比如你需要指定字段别名、调整字段顺序时。
• 结构体中存在NULL值，BigQuery无法自动推导NULL的具体类型（比如NULL是STRING还是TIMESTAMP），必须显式声明。
• 构造嵌套结构体、数组结构体这类复杂类型时，避免BigQuery自动推导的类型和目标类型不匹配。

左值有类型仍需要显式声明的原因： #

BigQuery是强类型编译型引擎，右值的类型必须在编译阶段完全确定，引擎不会自动根据左值类型做隐式的结构体类型转换。STRUCT的类型由「字段顺序、字段名、字段类型」三者共同决定，只要有一个属性不匹配，就会被判定为不同类型，因此必须显式声明保证右值类型和左值完全一致。

4. 嵌套结构的拆分建议和更新最佳实践 #

是否需要拆分为多张表： #

完全取决于你的查询模式：

• 如果你的业务经常需要查询、更新数组中的单个元素，或者需要关联其他表查询things中的字段，推荐拆分为父子表：主表存储id等顶层字段，子表存储每个thing的所有字段，用主表id做外键关联，更新、查询、聚合的语法都会更简单，性能也更好。
• 如果你的查询都是每次读取整个id对应的所有things集合，很少单独操作单个thing，保留嵌套结构更省存储，查询时不需要关联，性能也更高。

嵌套结构更新的最佳实践： #

• 优先用ARRAY_CONCAT、ARRAY_APPEND、ARRAY_INSERT这类语义明确的数组函数代替||运算符，可读性更强，也更容易排查问题。
• 如果只是更新数组中的部分元素，不要全量重写整个数组，用ARRAY(SELECT ... FROM UNNEST(things) ...)的方式遍历数组更新目标元素，性能更好，也避免误操作。
• 构造复杂结构体前先单独运行SELECT语句验证类型匹配，再放到UPDATE语句中，避免UPDATE语句全表扫描到一半才报错，浪费资源。

内容的提问来源于stack exchange，提问作者Dave Chambers