Node.js对接PostgreSQL如何单SQL查询返回指定嵌套JSON结构

调整后的SQL语句如下：

WITH quest_with_options AS (
  -- 先聚合每个问题对应的选项列表
  SELECT
    q.test_id,
    q.question,
    ARRAY_AGG(o.option) AS options
  FROM quests q
  LEFT JOIN options o ON q.id = o.quest_id
  GROUP BY q.id, q.test_id
),
tests_formatted AS (
  -- 聚合每个测试对应的问题列表
  SELECT
    JSON_AGG(
      JSON_BUILD_OBJECT(
        'quest', qwo.question,
        'options', qwo.options
      )
    ) AS test_list
  FROM tests t
  LEFT JOIN quest_with_options qwo ON t.id = qwo.test_id
  WHERE t.course_id = $1
),
lessons_formatted AS (
  -- 聚合课程对应的lesson列表，默认取所有测试的chapter去重，可根据实际表结构调整
  SELECT ARRAY_AGG(DISTINCT t.chapter) AS lesson_list
  FROM tests t
  WHERE t.course_id = $1
)
-- 拼接为最终结构
SELECT JSON_BUILD_OBJECT(
  'lessons', COALESCE(lf.lesson_list, ARRAY[]::text[]),
  'tests', COALESCE(tf.test_list, ARRAY[]::json[])
) AS result
FROM tests_formatted tf, lessons_formatted lf;

核心调整说明 #

• 采用嵌套分层聚合，先处理每个问题对应的选项，再聚合为测试列表，解决原SQL中问题和选项数组无法一一对应的问题
• 使用PostgreSQL原生JSON构造与聚合函数，直接返回符合要求的JSON结构，无需在Node.js层做二次数据处理
• 用COALESCE兼容无数据的边界场景，避免返回null值
• 注意实际使用时请用参数化查询传入courseId（上述SQL中用$1占位），避免SQL注入风险

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