BigQuery：聚合JSON中的去重值

可以通过原生SQL查询实现这类聚合，无需依赖Python远程函数，以下以PostgreSQL为例给出具体实现逻辑，其他主流数据库（如MySQL）也有类似的函数支持：

步骤拆解与SQL示例 #

假设你的表名为data_table，分组列是group_col，存储单层JSON的列是json_col：

1. 拆分JSON为键值对行：使用json_each_text函数将每行的JSON拆分成独立的键-值记录
2. 分组去重并聚合值列表：按分组列和JSON键分组，对值去重后聚合为数组
3. 重组为目标JSON对象：将每个分组下的键和对应值数组合并成最终的JSON对象

SELECT
    group_col,
    json_object_agg(key, value_list) AS aggregated_json
FROM (
    SELECT
        group_col,
        key,
        array_agg(DISTINCT value) AS value_list
    FROM data_table,
         json_each_text(json_col)
    GROUP BY group_col, key
) AS grouped_kv
GROUP BY group_col;

针对你给出的示例数据，执行后会得到预期结果：

{"key1": ["value1"], "key2": ["value2", "value22"], "key3": ["value33"]}

其他数据库的适配思路 #

• MySQL：使用JSON_TABLE拆分JSON为行，再通过JSON_ARRAYAGG(DISTINCT ...)聚合值，最后用JSON_OBJECTAGG重组JSON
• BigQuery：用UNNEST(JSON_EXTRACT_ARRAY(json_col))配合键值提取逻辑，再完成聚合重组

性能考量 #

对于数百万行的表，原生SQL的执行效率远高于Python远程函数——数据库查询优化器可针对分组、聚合操作做索引优化，而UDF会引入额外的序列化/反序列化开销，且难以利用数据库的并行处理能力。

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