如何SQL关联查询quiz_question与quiz_answer生成指定嵌套JSON结构

现有SQL的问题 #

你当前使用的INNER JOIN会将每个问题和它关联的每一条答案做连接，导致同一个问题重复出现多次，无法直接得到单条问题挂载所有答案的嵌套结构，同时你查询的字段也缺失了问题ID、答案内容等生成目标JSON必须的字段。

方案1：数据库层直接生成目标JSON（无需额外应用层处理） #

不同数据库的JSON聚合函数略有差异，对应写法如下：

MySQL 5.7及以上版本 #

SELECT JSON_OBJECT(
    'questions', JSON_ARRAYAGG(
        JSON_OBJECT(
            'id', q.id,
            'question', q.question,
            'type', q.type,
            'answers', (
                SELECT JSON_ARRAYAGG(a.answer)
                FROM quiz_answer a
                WHERE a.question_id = q.id
            )
        )
    )
) as json_result
FROM quiz_question q;

PostgreSQL版本 #

SELECT json_build_object(
    'questions', json_agg(
        json_build_object(
            'id', q.id,
            'question', q.question,
            'type', q.type,
            'answers', (
                SELECT json_agg(a.answer)
                FROM quiz_answer a
                WHERE a.question_id = q.id
            )
        )
    )
) as json_result
FROM quiz_question q;

SQL Server版本 #

SELECT 
    (
        SELECT 
            q.id, 
            q.question, 
            q.type,
            (
                SELECT a.answer
                FROM quiz_answer a
                WHERE a.question_id = q.id
                FOR JSON PATH
            ) as answers
        FROM quiz_question q
        FOR JSON PATH, ROOT('questions')
    ) as json_result

如果你的问题表实际包含helptext、imageurl等扩展字段，直接在JSON构造的部分添加对应字段映射即可。如果需要把每个答案包装为对象格式，调整子查询中答案的聚合逻辑，把字符串聚合改为对象聚合即可。

方案2：应用层组装（兼容所有数据库） #

如果不想依赖数据库专属的JSON函数，可以先查询结构化数据后在业务代码中组装：

SELECT 
    q.id,
    q.question,
    q.type,
    GROUP_CONCAT(a.answer SEPARATOR '|||') as answer_list
FROM quiz_question q
LEFT JOIN quiz_answer a ON q.id = a.question_id
GROUP BY q.id, q.question, q.type;

拿到查询结果后，按分隔符|||拆分answer_list字段得到每个问题的答案数组，再按要求拼接为目标JSON即可。

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