如何在BigQuery中动态合并以Tag开头的列为JSON格式？

要实现将所有以Tag_开头的列动态合并为JSON格式的Tag列，同时保留其他原有列，可以使用动态SQL结合INFORMATION_SCHEMA自动获取列名，无需手动指定列。以下是具体实现方案：

实现代码 #

假设你的表位于project.dataset.your_table_name，执行以下SQL即可：

DECLARE tag_columns ARRAY<STRING>;
DECLARE non_tag_columns ARRAY<STRING>;
DECLARE query STRING;

-- 提取所有以Tag_开头的列名
SET tag_columns = ARRAY(
  SELECT column_name
  FROM `project.dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'your_table_name'
    AND column_name LIKE 'Tag_%'
);

-- 提取非Tag_开头的列名
SET non_tag_columns = ARRAY(
  SELECT column_name
  FROM `project.dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'your_table_name'
    AND column_name NOT LIKE 'Tag_%'
);

-- 构造动态查询语句
SET query = FORMAT("""
  SELECT
    %s,
    TO_JSON_STRING(STRUCT(%s)) AS Tag
  FROM `project.dataset.your_table_name`
""", 
  ARRAY_TO_STRING(non_tag_columns, ', '),
  ARRAY_TO_STRING(tag_columns, ', ')
);

-- 执行动态查询
EXECUTE IMMEDIATE query;

代码说明 #

1. 自动获取列名：通过INFORMATION_SCHEMA.COLUMNS系统表筛选目标列，无需手动维护列名列表，实现动态适配表结构变化。
2. 生成JSON格式：用STRUCT()将所有Tag_开头的列组合成结构体，再通过TO_JSON_STRING()转换为JSON字符串，完全匹配你期望的输出格式。
3. 动态执行查询：通过EXECUTE IMMEDIATE执行拼接好的语句，返回包含原有列和新Tag列的结果集，空值会自动转为''（如示例中Harry的Tag_state）。

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