如何编写SQL查询从表字段生成指定结构的JSON对象？

原数据表 #

需求：生成JSON对象 {"A": 10, "B": -5}，其中键对应value1/value2的值，值根据sign调整qty的正负（"positive"为正，"negative"为负）。

以下是主流数据库的实现方案：

PostgreSQL #

使用json_build_object构造JSON对象：

SELECT json_build_object(
    value1, CASE sign1 WHEN 'positive' THEN qty1 ELSE -qty1 END,
    value2, CASE sign2 WHEN 'positive' THEN qty2 ELSE -qty2 END
) AS result_json
FROM your_table;

MySQL #

使用JSON_OBJECT函数：

SELECT JSON_OBJECT(
    value1, IF(sign1 = 'positive', qty1, -qty1),
    value2, IF(sign2 = 'positive', qty2, -qty2)
) AS result_json
FROM your_table;

SQL Server #

版本2022及以上（支持JSON_OBJECT） #

SELECT JSON_OBJECT(
    value1: CASE sign1 WHEN 'positive' THEN qty1 ELSE -qty1 END,
    value2: CASE sign2 WHEN 'positive' THEN qty2 ELSE -qty2 END
) AS result_json
FROM your_table;

旧版本（兼容SQL Server 2016+，使用FOR JSON PATH） #

SELECT (
    SELECT 
        CASE sign1 WHEN 'positive' THEN qty1 ELSE -qty1 END AS [A],
        CASE sign2 WHEN 'positive' THEN qty2 ELSE -qty2 END AS [B]
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS result_json
FROM your_table;

注：如果数据表有多行数据，需要根据实际需求调整聚合逻辑（比如使用json_agg等函数合并结果），上述方案针对单条数据场景。

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