如何在BigQuery中按前缀合并列生成指定结构JSON？

固定列数的手动写法 #

如果你的a_前缀列数量不多，直接用STRUCT()函数手动构造嵌套对象，把对应字段映射到a对象下即可：

WITH selectdata AS (
    SELECT 
        STRUCT(
            a_firstname AS firstname,
            a_middlename AS middlename,
            a_lastname AS lastname
        ) AS a
    FROM `account_id.Dataset.Table_name`
)
SELECT TO_JSON_STRING(t) AS json_data FROM selectdata AS t;

生成的JSON格式示例：

{"a":{"firstname":"xxx","middlename":"xxx","lastname":"xxx"}}

列数较多的动态写法 #

如果a_前缀的列数量多，不想逐个手动编写，可以用动态SQL自动生成嵌套结构：

DECLARE struct_fields STRING;

-- 自动获取所有a_开头的列，并处理成STRUCT需要的字段格式
SET struct_fields = (
    SELECT STRING_AGG(
        CONCAT('`', column_name, '` AS ', REPLACE(column_name, 'a_', '')),
        ', '
    )
    FROM `account_id.Dataset.INFORMATION_SCHEMA.COLUMNS`
    WHERE table_name = 'Table_name'
      AND column_name LIKE 'a_%'
);

-- 执行动态生成的查询
EXECUTE IMMEDIATE FORMAT("""
    WITH selectdata AS (
        SELECT STRUCT(%s) AS a
        FROM `account_id.Dataset.Table_name`
    )
    SELECT TO_JSON_STRING(t) AS json_data FROM selectdata AS t;
""", struct_fields);

这个方法会自动遍历表中所有a_开头的列，统一嵌套到a对象下，字段名自动去掉a_前缀。

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