如何在SQL中将多行数据实时合并为类JSON格式？

原始数据 #

方案1：生成以label_id为key的嵌套JSON #

针对主流数据库的实现如下：

MySQL 8.0+/MariaDB 10.5+ #

利用JSON_OBJECTAGG实现key-value形式的聚合：

SELECT 
    name,
    JSON_OBJECTAGG(
        label_id,
        JSON_OBJECT('label_prop1', label_prop1, 'label_prop2', label_prop2)
    ) AS labels
FROM your_table
GROUP BY name;

PostgreSQL 12+ #

使用jsonb_object_agg构建嵌套JSON对象：

SELECT 
    name,
    jsonb_object_agg(
        label_id,
        jsonb_build_object('label_prop1', label_prop1, 'label_prop2', label_prop2)
    ) AS labels
FROM your_table
GROUP BY name;
-- 如需转为字符串格式，可在末尾加::text

SQL Server 2016+ #

通过子查询配合FOR JSON语法实现：

SELECT 
    name,
    (
        SELECT label_id AS [key],
               JSON_OBJECT('label_prop1': label_prop1, 'label_prop2': label_prop2) AS [value]
        FROM your_table t2
        WHERE t2.name = t1.name
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) AS labels
FROM your_table t1
GROUP BY name;

方案2：生成标签对象数组 #

如果接受数组形式的JSON，实现更简单：

MySQL 8.0+/MariaDB 10.5+ #

用JSON_ARRAYAGG直接聚合为数组：

SELECT 
    name,
    JSON_ARRAYAGG(
        JSON_OBJECT('label_id', label_id, 'label_prop1', label_prop1, 'label_prop2', label_prop2)
    ) AS labels
FROM your_table
GROUP BY name;

PostgreSQL 12+ #

使用jsonb_agg生成JSON数组：

SELECT 
    name,
    jsonb_agg(
        jsonb_build_object('label_id', label_id, 'label_prop1', label_prop1, 'label_prop2', label_prop2)
    ) AS labels
FROM your_table
GROUP BY name;
-- 转字符串加::text

SQL Server 2016+ #

子查询配合FOR JSON PATH直接生成数组：

SELECT 
    name,
    (
        SELECT label_id, label_prop1, label_prop2
        FROM your_table t2
        WHERE t2.name = t1.name
        FOR JSON PATH
    ) AS labels
FROM your_table t1
GROUP BY name;

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