使用SQL实现按ID分组并将多行数据合并为单个JSON列

这个需求本质上是分组聚合+JSON序列化，不同SQL数据库提供了不同的内置函数来快速实现，下面是主流数据库的具体写法：

1. PostgreSQL #

PostgreSQL原生支持JSON处理，用json_build_object把每行的col1和col2转成JSON对象，再用json_agg把同组的对象聚合为JSON数组：

-- 测试数据创建（可选）
CREATE TABLE test_table (
    id INT,
    col1 VARCHAR(10),
    col2 VARCHAR(10)
);
INSERT INTO test_table VALUES
(1, 'a', 'b'),
(1, 'c', 'd'),
(2, 'e', 'f'),
(2, 'g', 'h');

-- 查询语句
SELECT
    id,
    json_agg(json_build_object('col1', col1, 'col2', col2)) AS combined
FROM test_table
GROUP BY id;

执行后就能得到你想要的结果：

2. MySQL #

MySQL 5.7+支持JSON函数，用JSON_OBJECT生成单个JSON对象，再用JSON_ARRAYAGG聚合为数组：

-- 测试数据创建（可选）
CREATE TABLE test_table (
    id INT,
    col1 VARCHAR(10),
    col2 VARCHAR(10)
);
INSERT INTO test_table VALUES
(1, 'a', 'b'),
(1, 'c', 'd'),
(2, 'e', 'f'),
(2, 'g', 'h');

-- 查询语句
SELECT
    id,
    JSON_ARRAYAGG(JSON_OBJECT('col1', col1, 'col2', col2)) AS combined
FROM test_table
GROUP BY id;

注意：如果你的MySQL版本低于5.7，可能需要用字符串拼接的方式手动构造JSON，不过建议优先升级到支持JSON的版本。

3. SQL Server #

SQL Server 2016+支持JSON处理，这里需要用STRING_AGG拼接每个行的JSON字符串，再手动包裹数组符号，最后用JSON_QUERY确保结果是合法的JSON类型：

-- 测试数据创建（可选）
CREATE TABLE test_table (
    id INT,
    col1 VARCHAR(10),
    col2 VARCHAR(10)
);
INSERT INTO test_table VALUES
(1, 'a', 'b'),
(1, 'c', 'd'),
(2, 'e', 'f'),
(2, 'g', 'h');

-- 查询语句
SELECT
    id,
    JSON_QUERY('[' + STRING_AGG(JSON_QUERY('{"col1":"' + col1 + '","col2":"' + col2 + '"}'), ',') + ']') AS combined
FROM test_table
GROUP BY id;

如果是SQL Server 2022+，可以用更简洁的原生聚合函数：

SELECT
    id,
    JSON_ARRAYAGG(JSON_OBJECT('col1': col1, 'col2': col2)) AS combined
FROM test_table
GROUP BY id;

4. Oracle #

Oracle 12cR2+支持JSON函数，用JSON_OBJECT生成对象，JSON_ARRAYAGG聚合为数组：

-- 测试数据创建（可选）
CREATE TABLE test_table (
    id NUMBER,
    col1 VARCHAR2(10),
    col2 VARCHAR2(10)
);
INSERT INTO test_table VALUES
(1, 'a', 'b'),
(1, 'c', 'd'),
(2, 'e', 'f'),
(2, 'g', 'h');

-- 查询语句
SELECT
    id,
    JSON_ARRAYAGG(JSON_OBJECT('col1' VALUE col1, 'col2' VALUE col2)) AS combined
FROM test_table
GROUP BY id;

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