在BigQuery中将列表列转换为ARRAY类型的方法求助

核心结论 #

BigQuery完全支持将这类格式的字符串列转换为ARRAY类型，同时也支持对多个包含类似列表格式的列执行转换操作。

单个列的转换与展开 #

1. 将字符串转换为ARRAY类型 #

如果只是要把col2的字符串格式转为BigQuery的ARRAY<FLOAT64>类型，可使用以下SQL：

SELECT
  col1,
  -- 清理字符串并转换为FLOAT64数组
  ARRAY(
    SELECT CAST(TRIM(val) AS FLOAT64)
    FROM UNNEST(SPLIT(REGEXP_REPLACE(col2, r'^\[|\]$', ''), ' ')) val
    WHERE TRIM(val) != '' -- 过滤连续空格导致的空元素
  ) AS col2
FROM
  `你的项目ID.你的数据集ID.你的表名`

• 逻辑说明：
  • REGEXP_REPLACE(col2, r'^\[|\]$', '')：移除字符串首尾的[和]
  • SPLIT(..., ' ')：按空格分割字符串为元素列表
  • TRIM(val) != ''：过滤空格分割产生的空元素
  • CAST(TRIM(val) AS FLOAT64)：将每个元素转为浮点型
  • ARRAY(...)：将处理后的元素组合为数组

2. 展开数组为多行（匹配预期输出） #

如果要把数组展开为每行对应一个元素的格式，使用UNNEST：

SELECT
  col1,
  val AS col2
FROM
  `你的项目ID.你的数据集ID.你的表名`,
  UNNEST(
    ARRAY(
      SELECT CAST(TRIM(val) AS FLOAT64)
      FROM UNNEST(SPLIT(REGEXP_REPLACE(col2, r'^\[|\]$', ''), ' ')) val
      WHERE TRIM(val) != ''
    )
  ) val

多个列表列的处理 #

如果表中有多个类似格式的列（比如col2、col3都是这类字符串列表），可根据需求选择以下处理方式：

方式1：同时转换为数组（保留每行结构） #

SELECT
  col1,
  -- 转换col2为数组
  ARRAY(
    SELECT CAST(TRIM(val) AS FLOAT64)
    FROM UNNEST(SPLIT(REGEXP_REPLACE(col2, r'^\[|\]$', ''), ' ')) val
    WHERE TRIM(val) != ''
  ) AS col2,
  -- 转换col3为数组
  ARRAY(
    SELECT CAST(TRIM(val) AS FLOAT64)
    FROM UNNEST(SPLIT(REGEXP_REPLACE(col3, r'^\[|\]$', ''), ' ')) val
    WHERE TRIM(val) != ''
  ) AS col3
FROM
  `你的项目ID.你的数据集ID.你的表名`

方式2：展开多个数组（元素一一对应） #

如果需要让多个数组的元素按位置一一对应展开（比如col2的第i个元素和col3的第i个元素在同一行），使用WITH OFFSET：

SELECT
  col1,
  val2 AS col2,
  val3 AS col3
FROM
  `你的项目ID.你的数据集ID.你的表名`,
  UNNEST(
    ARRAY(
      SELECT CAST(TRIM(val) AS FLOAT64)
      FROM UNNEST(SPLIT(REGEXP_REPLACE(col2, r'^\[|\]$', ''), ' ')) val
      WHERE TRIM(val) != ''
    )
  ) val2 WITH OFFSET pos2,
  UNNEST(
    ARRAY(
      SELECT CAST(TRIM(val) AS FLOAT64)
      FROM UNNEST(SPLIT(REGEXP_REPLACE(col3, r'^\[|\]$', ''), ' ')) val
      WHERE TRIM(val) != ''
    )
  ) val3 WITH OFFSET pos3
WHERE pos2 = pos3

方式3：展开多个数组（所有元素组合） #

如果需要获取多个数组元素的所有组合，直接使用多个UNNEST即可：

SELECT
  col1,
  val2 AS col2,
  val3 AS col3
FROM
  `你的项目ID.你的数据集ID.你的表名`,
  UNNEST(
    ARRAY(
      SELECT CAST(TRIM(val) AS FLOAT64)
      FROM UNNEST(SPLIT(REGEXP_REPLACE(col2, r'^\[|\]$', ''), ' ')) val
      WHERE TRIM(val) != ''
    )
  ) val2,
  UNNEST(
    ARRAY(
      SELECT CAST(TRIM(val) AS FLOAT64)
      FROM UNNEST(SPLIT(REGEXP_REPLACE(col3, r'^\[|\]$', ''), ' ')) val
      WHERE TRIM(val) != ''
    )
  ) val3

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