如何编写SQL将题目及其所有选项输出到同一个JSON对象中

实现思路 #

直接用数据库原生的JSON聚合函数替代string_agg字符串拼接，按题目ID分组后将对应选项聚合为JSON数组/对象，最终每道题对应一条JSON记录。
注意：JSON标准不允许同一对象内存在重复键，你提到的{"question":"5+5=?", "choice":"1", "choice":"2"...}格式不符合规范，重复key会被后续值覆盖，推荐使用嵌套choices数组/带序号key的对象格式。

PostgreSQL 实现 #

选项聚合为数组 #

SELECT json_build_object(
    'question', q.question,
    'choices', json_agg(qc.choice)
) AS question_json
FROM question q
JOIN question_choice qc ON q.id = qc.question_id
GROUP BY q.id, q.question;

返回结果示例：

{"question":"5+5=?", "choices":["7","8","9","10","11"]}

选项聚合为带序号键的对象 #

SELECT json_build_object(
    'question', q.question,
    'choices', json_object_agg(
        'choice' || ROW_NUMBER() OVER (PARTITION BY qc.question_id ORDER BY qc.id), 
        qc.choice
    )
) AS question_json
FROM question q
JOIN question_choice qc ON q.id = qc.question_id
GROUP BY q.id, q.question;

返回结果示例：

{"question":"5+5=?", "choices":{"choice1":"7","choice2":"8","choice3":"9","choice4":"10","choice5":"11"}}

MySQL 5.7+ 实现 #

选项聚合为数组 #

SELECT JSON_OBJECT(
    'question', q.question,
    'choices', JSON_ARRAYAGG(qc.choice)
) AS question_json
FROM question q
JOIN question_choice qc ON q.id = qc.question_id
GROUP BY q.id, q.question;

选项聚合为带序号键的对象（MySQL 8.0+支持窗口函数） #

WITH ordered_choices AS (
    SELECT 
        qc.question_id,
        qc.choice,
        ROW_NUMBER() OVER (PARTITION BY qc.question_id ORDER BY qc.id) AS rn
    FROM question_choice qc
)
SELECT JSON_OBJECT(
    'question', q.question,
    'choices', JSON_OBJECTAGG(CONCAT('choice', rn), oc.choice)
) AS question_json
FROM question q
JOIN ordered_choices oc ON q.id = oc.question_id
GROUP BY q.id, q.question;

SQL Server 实现 #

SELECT 
    q.question,
    (SELECT choice FROM question_choice qc WHERE qc.question_id = q.id FOR JSON PATH) AS choices
FROM question q
FOR JSON PATH;

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