如何去除BigQuery JSON字符串中的重复标签？

问题场景 #

查询BigQuery成本数据导出表时，单个资源的标签出现重复，合并后生成的标签数组全是重复项，示例如下：

[{"key":"label1","value":"test"},{"key":"label2","value":"vm"},{"key":"label1","value":"test"},{"key":"label2","value":"vm"},{"key":"label1","value":"test"},{"key":"label2","value":"vm"},{"key":"label1","value":"test"},{"key":"label2","value":"vm"}]

当前使用的查询语句：

SELECT
    billing_account_id,
    service.id AS service_id,
    sku.id AS sku_id,
    FORMAT_DATETIME('%Y-%m-%d', usage_start_time) AS usage_date,
    location.location AS location,
    project.id AS project_id,
    project.number AS project_number,
    resource.name AS resource_name,
    resource.global_name AS res_global_name,
    TO_JSON_STRING(ARRAY_CONCAT_AGG(labels)) AS labels
from 
    ${TABLE_NAME}
where 
    usage_start_time>=${time} and usage_end_time<=${time}
group by 
    billing_account_id, 
    service_id,
    sku_id,
    usage_date,
    location,
    project_id,
    project_number,
    resource_name,
    res_global_name

解决方案 #

原语句用ARRAY_CONCAT_AGG(labels)直接拼接所有行的标签数组，必然会产生重复项。要得到唯一标签组合，需要先拆分标签、去重，再重新聚合。

基础去重（保留唯一键值对） #

修改后的查询语句：

SELECT
    billing_account_id,
    service.id AS service_id,
    sku.id AS sku_id,
    FORMAT_DATETIME('%Y-%m-%d', usage_start_time) AS usage_date,
    location.location AS location,
    project.id AS project_id,
    project.number AS project_number,
    resource.name AS resource_name,
    resource.global_name AS res_global_name,
    TO_JSON_STRING(ARRAY_AGG(DISTINCT label)) AS labels
FROM 
    ${TABLE_NAME},
    UNNEST(labels) AS label
WHERE 
    usage_start_time>=${time} and usage_end_time<=${time}
GROUP BY 
    billing_account_id, 
    service_id,
    sku_id,
    usage_date,
    location,
    project_id,
    project_number,
    resource_name,
    res_global_name

核心改动说明

• UNNEST(labels)把每行的标签数组拆分成单独的键值对行
• DISTINCT label确保聚合时只保留完全唯一的{key,value}组合
• ARRAY_AGG把去重后的标签重新组合成数组，再转成JSON字符串

进阶：合并同一key的不同value #

如果同一资源的同一个标签key对应多个不同value（比如label1同时有test和prod），需要把这些value合并到同一个key下，用逗号分隔的话，使用以下语句：

SELECT
    billing_account_id,
    service.id AS service_id,
    sku.id AS sku_id,
    FORMAT_DATETIME('%Y-%m-%d', usage_start_time) AS usage_date,
    location.location AS location,
    project.id AS project_id,
    project.number AS project_number,
    resource.name AS resource_name,
    resource.global_name AS res_global_name,
    TO_JSON_STRING(ARRAY_AGG(STRUCT(label.key AS key, STRING_AGG(DISTINCT label.value, ', ') AS value))) AS labels
FROM 
    ${TABLE_NAME},
    UNNEST(labels) AS label
WHERE 
    usage_start_time>=${time} and usage_end_time<=${time}
GROUP BY 
    billing_account_id, 
    service_id,
    sku_id,
    usage_date,
    location,
    project_id,
    project_number,
    resource_name,
    res_global_name,
    label.key

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