BigQuery标准SQL中如何比较含嵌套记录的数组

针对嵌套数组类型无法直接用等值运算符、无法直接参与UNION DISTINCT去重比对的问题，核心解决思路是将数组字段转换为标准化、可直接比较的字符串比对指纹，完全匹配Python中忽略元素顺序、逐字段校验内容的比对效果，可同时覆盖数组长度差异、数组内元素属性差异两类场景。

核心实现逻辑 #

• 对两个表中每一条人员记录，独立生成Children数组的标准化比对指纹：
  • 先将数组中的嵌套记录拆分为独立的单行数据
  • 对每个嵌套记录，按固定的字段顺序拼接为无歧义的字符串（固定字段顺序是为了避免嵌套结构的字段排序差异导致的比对偏差）
  • 对拼接完成的子女字符串做字典序排序，消除原数组元素先后顺序不同带来的误判
  • 用不会出现在字段值中的固定分隔符，将排序后的字符串拼接为单条长文本，作为该数组的唯一比对标识
• 以人员唯一标识（示例中为Name字段）为关联键关联两个表，直接对比两边生成的指纹字符串，值不一致的即为Children字段存在差异的记录。

可直接落地的参考代码 #

WITH table_a_fingerprint AS (
  SELECT
    Name,
    ARRAY_TO_STRING(
      ARRAY(
        SELECT CONCAT('{Name:', child.Name, ',Age:', child.Age, '}')
        FROM UNNEST(Children) AS child
        ORDER BY 1
      ),
      '||'
    ) AS children_fingerprint
  FROM tableA
),
table_b_fingerprint AS (
  SELECT
    Name,
    ARRAY_TO_STRING(
      ARRAY(
        SELECT CONCAT('{Name:', child.Name, ',Age:', child.Age, '}')
        FROM UNNEST(Children) AS child
        ORDER BY 1
      ),
      '||'
    ) AS children_fingerprint
  FROM tableB
)
SELECT a.Name
FROM table_a_fingerprint a
INNER JOIN table_b_fingerprint b
  ON a.Name = b.Name
WHERE a.children_fingerprint != b.children_fingerprint;

逻辑适配说明 #

• 代码可直接在支持UNNEST数组拆包、ARRAY聚合的SQL引擎（如BigQuery、Trino、Snowflake、PostgreSQL 14+）上运行，仅需根据引擎的内置函数名做微调（比如PostgreSQL中对应的数组聚合函数为array_agg）
• 比对结果完全覆盖需求场景：
  • 数组长度差异（如Adam的表A为2个子女、表B为3个子女，生成的指纹长度天然不同）
  • 数组元素属性差异（如Bob的表A中4岁子女为William、2岁为Rose，表B中为George、Frank，生成的字符串内容存在差异）
  • 自动忽略数组元素顺序差异（比如同一组子女在两个表中的数组排列顺序不同，只要子女姓名、年龄完全一致，排序后生成的指纹完全相同，不会被误判为差异）

简化场景的快捷实现 #

如果使用的SQL引擎支持自定义顺序的JSON序列化函数，可省去手动拆包、拼接字符串的步骤，直接生成标准化JSON字符串作为比对指纹：

SELECT a.Name
FROM tableA a
JOIN tableB b ON a.Name = b.Name
WHERE TO_JSON(a.Children, true) != TO_JSON(b.Children, true)

注意：如果业务场景要求“数组元素顺序不同但内容一致不算差异”，使用原生JSON序列化函数时必须先对数组元素做排序后再序列化，否则原生序列化会严格保留原数组的元素顺序，出现不必要的误判。

常见失效方法的原因说明 #

• 直接用!=/<>运算符比对数组：多数SQL引擎的数组等值比较默认要求元素逐位匹配，对元素顺序的敏感度极高，且部分引擎根本未开放原生数组比较能力
• 直接使用UNION DISTINCT比对数组：DISTINCT去重逻辑对数组类型的哈希计算支持不完善，经常出现内容完全一致的数组被判定为不同值的情况，比对结果可靠性不足。

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