BigQuery报错：数组索引1越界，求所有列去重计数解决方案

我在BigQuery中有一张包含多列的表，想要生成一张新表，展示每一列的去重值计数和具体的去重值集合。用了一段不需要指定列名或循环的代码，但触发了错误：Array index 1 is out of bounds (overflow)，尝试添加WHERE x[1] IS NOT NULL这类条件也没用，求解决方法。

原代码如下：

SELECT key AS column_name,
COUNT(DISTINCT val) AS number_of_options,
ARRAY_AGG(DISTINCT val) AS distinct_options
FROM (
  SELECT TRIM(x[0], '"') AS key, 
  TRIM(x[1], '"') AS val
  FROM t,
  UNNEST(SPLIT(TRIM(TO_JSON_STRING(t), '{}'), ',"')) kv,   
  UNNEST([STRUCT(SPLIT(kv, '":') AS x)])
  )
GROUP BY key  

问题原因 #

原方法靠TO_JSON_STRING把行转成JSON字符串后拆分，但存在两个致命问题：

1. 拆分逻辑脆弱：当字段值包含冒号、引号这类JSON特殊字符时，SPLIT(kv, '":')会把值拆成多个部分，导致生成的数组x长度不足2，访问x[1]就会触发越界错误。
2. 空值处理不当：如果字段值是NULL，JSON里会显示为"key":null，拆分后x[1]是null，但原代码的TRIM操作无法识别，而且提前过滤x[1] IS NOT NULL会直接丢失空值的统计。

修正后的代码 #

改用正则表达式匹配JSON中的键值对，避免特殊字符导致的拆分异常，同时完善空值处理：

SELECT 
  key AS column_name,
  COUNT(DISTINCT val) AS number_of_options,
  ARRAY_AGG(DISTINCT val IGNORE NULLS) AS distinct_options
FROM (
  SELECT
    -- 提取键名，去掉前后引号
    REGEXP_EXTRACT(kv, r'"([^"]+)"') AS key,
    -- 处理值：区分空值和普通值，去掉字符串类型的引号
    CASE 
      WHEN REGEXP_EXTRACT(kv, r':(.*)') = 'null' THEN NULL
      ELSE TRIM(REGEXP_EXTRACT(kv, r':(.*)'), '"') 
    END AS val
  FROM t,
  -- 正则匹配所有"键":值的结构，直到下一个,"或JSON结尾
  UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"([^"]+)":(.*?)(?=,"|}$)')) kv
)
WHERE key IS NOT NULL
GROUP BY key

代码说明 #

1. 正则提取键值对：用REGEXP_EXTRACT_ALL匹配JSON中完整的键值单元，避开值内部的特殊字符干扰，保证每个kv都是完整的"列名":值结构。
2. 键值分离：用REGEXP_EXTRACT分别提取键和值，避免手动拆分的不可靠性。
3. 空值处理：专门判断值为null的情况，转为BigQuery的NULL，同时在ARRAY_AGG中用IGNORE NULLS排除空值元素。
4. 兼容多数据类型：对于数字、布尔值等非字符串类型，TO_JSON_STRING会保留原始格式，TRIM引号不会影响其显示，确保统计结果准确。

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