BigQuery中动态拼接任意数量字段并简化IFNULL使用的方法

方法1：利用JSON序列化与正则提取 #

无需提前指定字段，直接通过JSON解析处理整行数据，统一拼接字段值：

SELECT
  ARRAY_TO_STRING(
    ARRAY(
      SELECT 
        CASE 
          WHEN value = 'null' THEN 'NULL'
          ELSE TRIM(value, '"')
        END
      FROM UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"[^"]+":([^,}]*)')) AS value
    ),
    '@'
  ) AS fieldsUnion
FROM MY_DATASET.TABLE_ONE t
LIMIT 1

说明：

• TO_JSON_STRING(t)将整行数据转为JSON格式，自动处理不同类型字段的序列化
• 正则表达式提取每个字段对应的值部分，区分null（原字段为NULL）和带引号的字符串值
• 通过CASE语句将JSON中的null替换为你需要的'NULL'，同时去除字符串值的引号
• 最后用ARRAY_TO_STRING将所有处理后的值用@拼接成目标字符串

方法2：动态SQL（精准适配字段类型） #

如果需要更严谨的类型转换（避免正则可能出现的边缘情况），可以通过元数据动态生成SQL：

DECLARE sql STRING;
SET sql = (
  SELECT CONCAT(
    'SELECT ARRAY_TO_STRING([', 
    STRING_AGG(CONCAT('IFNULL(CAST(', column_name, ' AS STRING), "NULL")'), ', '),
    '], "@") AS fieldsUnion FROM MY_DATASET.TABLE_ONE LIMIT 1'
  )
  FROM `MY_DATASET.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'TABLE_ONE'
);
EXECUTE IMMEDIATE sql;

说明：

• 从INFORMATION_SCHEMA.COLUMNS获取目标表的所有字段名
• 自动为每个字段生成IFNULL(CAST(字段 AS STRING), "NULL")语句，确保字段转字符串并处理NULL
• 通过EXECUTE IMMEDIATE执行动态生成的SQL，完全适配表的字段变化

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