BigQuery处理含逗号数据时SQL查询结果不符合预期问题求助

原SQL存在的问题 #

• FROM子句未指定源表，且to_json_string()没有传入需要序列化的行对象参数，JSON解析逻辑无合法输入
• 过滤条件metric not in (ID)逻辑错误：需求是过滤键名为ID的解析结果，原写法是匹配ID字段的取值（比如ID=100时就是排除metric等于100的记录），不符合预期
• 直接用冒号拆分键值对时，如果值本身包含冒号会被错误截断，解析结果会出错

修正后SQL #

select
  ID,
  metric,
  value
from (
  SELECT
    t.*,
    REGEXP_REPLACE(SPLIT(pair, ':')[OFFSET(0)], r'^"|"$', '') AS metric,
    -- 兼容值中包含冒号的场景，避免截断
    REGEXP_REPLACE(
      ARRAY_TO_STRING(ARRAY_SLICE(SPLIT(pair, ':'), 1, ARRAY_LENGTH(SPLIT(pair, ':'))), ':'),
      r'^"|"$',
      ''
    ) AS value
  FROM
    -- 此处替换为你的实际源表名称
    your_source_table t,
    UNNEST(SPLIT(REGEXP_REPLACE(to_json_string(t), r'{|}', ''))) AS pair
)
-- 正确过滤掉键为ID的解析项
WHERE metric != 'ID'
ORDER BY metric, value;

额外适配说明 #

如果源表中digits字段为数组类型，解析后的值会自带[]符号，可将value的解析逻辑调整为如下内容，即可得到你预期的无括号逗号分隔格式：

REGEXP_REPLACE(
  REGEXP_REPLACE(
    ARRAY_TO_STRING(ARRAY_SLICE(SPLIT(pair, ':'), 1, ARRAY_LENGTH(SPLIT(pair, ':'))), ':'),
    r'^"|"$',
    ''
  ),
  r'^\[|\]$',
  ''
) AS value

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