如何在Node.js中编写SQL关联两表获取指定嵌套JSON结果

注：从给出的样例数据关联关系判断，Students表的id字段实际为关联teacher表主键的教师ID，属于逻辑外键，关联条件为teacher.id = Students.id。

方案1：SQL层JSON聚合 + Node.js轻量处理 #

适合使用MySQL 5.7+、PostgreSQL等支持原生JSON聚合函数的数据库场景，SQL层直接完成结构化聚合，Node.js侧仅需做少量类型适配。

SQL查询语句（MySQL版本） #

SELECT 
  t.Teacher_name,
  JSON_ARRAYAGG(
    JSON_OBJECT('Subject_name', LOWER(s.Subject_name))
  ) AS Subjects
FROM teacher t
LEFT JOIN Students s ON t.id = s.id
GROUP BY t.id, t.Teacher_name;

PostgreSQL可替换聚合函数为json_agg(json_build_object('Subject_name', LOWER(s.Subject_name)))，逻辑完全一致。

Node.js侧处理代码（以mysql2/promise驱动为例） #

// 提前初始化好数据库连接池pool
async function getTeacherWithSubjects() {
  const [rows] = await pool.query(`
    SELECT 
      t.Teacher_name,
      JSON_ARRAYAGG(
        JSON_OBJECT('Subject_name', LOWER(s.Subject_name))
      ) AS Subjects
    FROM teacher t
    LEFT JOIN Students s ON t.id = s.id
    GROUP BY t.id, t.Teacher_name
  `);

  // 兼容部分驱动返回JSON聚合字段为字符串的情况
  return rows.map(row => ({
    Teacher_name: row.Teacher_name,
    Subjects: typeof row.Subjects === 'string' ? JSON.parse(row.Subjects) : row.Subjects
  }));
}

方案2：平铺关联查询 + Node.js层聚合 #

适合使用SQLite、低版本MySQL等不支持JSON聚合函数的场景，先查询关联后的平铺数据，在业务代码层完成分组聚合。

SQL查询语句 #

SELECT 
  t.Teacher_name,
  LOWER(s.Subject_name) AS Subject_name
FROM teacher t
LEFT JOIN Students s ON t.id = s.id
ORDER BY t.id;

Node.js侧处理代码 #

async function getTeacherWithSubjects() {
  const [rows] = await pool.query(`
    SELECT 
      t.Teacher_name,
      LOWER(s.Subject_name) AS Subject_name
    FROM teacher t
    LEFT JOIN Students s ON t.id = s.id
    ORDER BY t.id
  `);

  const teacherMap = new Map();
  for (const row of rows) {
    // 初始化教师条目
    if (!teacherMap.has(row.Teacher_name)) {
      teacherMap.set(row.Teacher_name, {
        Teacher_name: row.Teacher_name,
        Subjects: []
      });
    }
    // 跳过无科目的空关联数据
    if (row.Subject_name) {
      teacherMap.get(row.Teacher_name).Subjects.push({
        Subject_name: row.Subject_name
      });
    }
  }

  return Array.from(teacherMap.values());
}

注意事项 #

• 两个方案均使用LEFT JOIN做关联，即使教师没有绑定对应科目，也会返回该教师条目，对应Subjects字段为空数组；如果业务上确认所有教师都有关联科目，可替换为INNER JOIN提升查询效率。
• 科目名转小写的逻辑放在SQL层通过LOWER()函数实现，比全量数据查出后在JS层遍历转换性能更优。
• 方案1中如果遇到JSON聚合字段返回字符串类型的问题，手动做一次JSON解析即可，不同数据库驱动的序列化逻辑存在差异。

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