如何对键为动态值的JSON字段中的值执行group by操作？

数据老陈

2026-8-8

按JSON字段值分组的实现方案

我有一个逗号分隔的字符串字段fieldA，还有一个JSON字段fieldB，它的键均来自fieldA的取值。需要按fieldB中的每个value执行group by操作，将对应key聚合为逗号分隔的字符串。

示例输入数据

fieldA   fieldB
a,b      {'a': 'value1', 'b': 'value3'}
c,d      {'c': 'value3', 'd': 'value4'}

期望输出结果

fieldx    fieldy
row1:  value1     a
row2:  value3     b,c
row3:  value4     d

实现方案

核心思路是先将fieldA拆分为单个key，关联fieldB中对应的value，形成单行键值对后，再按value分组聚合key。以下是主流数据库的具体实现：

PostgreSQL 版本

WITH key_value_pairs AS (
    SELECT
        unnest(string_to_array(fieldA, ',')) AS key_name,
        (fieldB ->> unnest(string_to_array(fieldA, ','))) AS value_name
    FROM your_table
)
SELECT
    value_name AS fieldx,
    string_agg(key_name, ',') AS fieldy
FROM key_value_pairs
GROUP BY value_name
ORDER BY fieldx;

MySQL 版本

WITH RECURSIVE split_keys AS (
    SELECT
        SUBSTRING_INDEX(fieldA, ',', 1) AS key_name,
        SUBSTRING(fieldA, LOCATE(',', fieldA) + 1) AS remaining_keys,
        fieldB
    FROM your_table
    WHERE fieldA IS NOT NULL AND fieldA != ''
    UNION ALL
    SELECT
        SUBSTRING_INDEX(remaining_keys, ',', 1) AS key_name,
        SUBSTRING(remaining_keys, LOCATE(',', remaining_keys) + 1) AS remaining_keys,
        fieldB
    FROM split_keys
    WHERE remaining_keys IS NOT NULL AND remaining_keys != ''
)
SELECT
    JSON_UNQUOTE(JSON_EXTRACT(fieldB, CONCAT('$.', key_name))) AS fieldx,
    GROUP_CONCAT(key_name ORDER BY key_name SEPARATOR ',') AS fieldy
FROM split_keys
GROUP BY fieldx
ORDER BY fieldx;