BigQuery SQL中拆分嵌套数组列获取string_1和string_2的问题

你遇到的问题核心有两点：

• UNNEST(table)写法错误，UNNEST需要传入数组类型的列名（比如你的嵌套列是strings，应该写UNNEST(strings)），而非表名
• 未过滤拆分后产生的NULL值，可能导致结果不符合预期

修正后的基础写法 #

假设你的嵌套数组列名为strings，可以用以下代码提取：

ARRAY(
  SELECT DISTINCT SPLIT(r, '/abc')[SAFE_ORDINAL(1)]
  FROM UNNEST(strings) r
  WHERE SPLIT(r, '/abc')[SAFE_ORDINAL(1)] IS NOT NULL
) AS string_1,
ARRAY(
  SELECT DISTINCT SPLIT(r, '/abc')[SAFE_ORDINAL(2)]
  FROM UNNEST(strings) r
  WHERE SPLIT(r, '/abc')[SAFE_ORDINAL(2)] IS NOT NULL
) AS string_2

更高效的优化写法 #

如果想避免重复计算拆分结果，可以先把拆分后的字段结构化，再聚合：

SELECT
  ARRAY_AGG(DISTINCT part1 IGNORE NULLS) AS string_1,
  ARRAY_AGG(DISTINCT part2 IGNORE NULLS) AS string_2
FROM
  your_table,
  UNNEST(strings) r,
  UNNEST([STRUCT(
    SPLIT(r, '/abc')[SAFE_ORDINAL(1)] AS part1,
    SPLIT(r, '/abc')[SAFE_ORDINAL(2)] AS part2
  )])

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