如何在BigQuery SQL中将含嵌套分隔符的列拆分为STRUCT数组？

你的原查询已经能实现需求，但可以从可读性、鲁棒性两个核心方向优化，同时贴合BigQuery的最佳实践：

优化后的查询写法 #

SELECT
  pmid,
  ARRAY_AGG(
    STRUCT(
      -- 用SAFE_OFFSET避免脏数据导致越界报错
      SAFE_OFFSET(SPLIT(mesh_entry, ':'), 0) AS code,
      SAFE_OFFSET(SPLIT(mesh_entry, ':'), 1) AS term
    )
    -- 过滤掉格式无效的条目，保证结果干净
    WHERE SAFE_OFFSET(SPLIT(mesh_entry, ':'), 0) IS NOT NULL
      AND SAFE_OFFSET(SPLIT(mesh_entry, ':'), 1) IS NOT NULL
  ) AS mesh_terms
FROM `omicidx_etl.pm1`,
UNNEST(SPLIT(mesh_terms, ';')) AS mesh_entry
GROUP BY pmid

具体改进点说明 #

• 精简查询层级：去掉了原查询中3层冗余的CTE，直接在主查询里完成拆分、UNNEST、聚合的全流程，逻辑更连贯，阅读时不用反复跳转临时表。
• 增强鲁棒性：原查询默认所有条目都能按:拆分成两个元素，但实际数据可能存在脏数据（比如无冒号、冒号前后为空）。用SAFE_OFFSET代替普通的OFFSET，能避免越界报错；同时加入过滤条件，只保留格式有效的条目，保证结果数据的可靠性。
• 语义化命名：把原查询中模糊的x、y换成mesh_entry，让每个变量的含义一目了然，后续维护或修改时成本更低。

补充说明 #

性能方面，BigQuery对这类简单字符串操作和聚合的优化已经很成熟，精简后的写法和原查询性能差异极小。但可读性和鲁棒性的提升，对团队协作、长期维护来说价值更大。

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