Node.js中如何分组PostgreSQL联表返回的测验应用数据？

场景与问题 #

测验应用中关联questions和choices表，执行联表查询后返回的是每条选项对应一条记录的格式，需要将数据按问题分组，把对应选项整合为数组。

表结构 #

CREATE TABLE questions (
  q_id BIGSERIAL PRIMARY KEY,
  question text
)

CREATE TABLE choices (
  c_id BIGSERIAL PRIMARY KEY,
  choice text,
  question_id BIGINT REFERENCES questions (q_id) -- 注：原代码中REFERENCES test1为笔误，应为questions
)

当前查询代码（含笔误） #

const getQuestionAndChoices = async (req, res) => {
  try {
    const getAllData = await pool.query(
        'select * from questions join choices on questions.q_id = answer.question_id') -- 注：answer应为choices

    return res.status(200).json(getAllData.rows)
  } catch (error) {
    return res.status(400).json(error.message)
  }
}

当前返回结果 #

[
    {
        "q_id": "1",
        "question": "question 1",
        "c_id": "1",
        "choice": "choice_1",
        "question_id": "1"
    },
    {
        "q_id": "1",
        "question": "question 1",
        "c_id": "2",
        "choice": "choice_2",
        "question_id": "1"
    },
    {
        "q_id": "2",
        "question": "question 2",
        "c_id": "3",
        "choice": "choice_1",
        "question_id": "2"
    },
    {
        "q_id": "2",
        "question": "question 2",
        "c_id": "4",
        "choice": "choice_2",
        "question_id": "2"
    }
]

期望结果 #

[
    {
        "q_id": "1",
        "question": "question 1",
        "choice": ["choice_1", "choice_2"],
        "question_id": "1"
    },
    {
        "q_id": "2",
        "question": "question 2",
        "choice": ["choice_1", "choice_2"],
        "question_id": "2"
    }
]

解决方案 #

方案一：JavaScript处理查询结果 #

通过reduce方法对查询返回的数组进行分组，将同一问题的选项整合为数组：

// 定义分组处理函数
const groupQuestionsWithChoices = (data) => {
  return Object.values(data.reduce((acc, item) => {
    const key = item.q_id;
    if (!acc[key]) {
      acc[key] = {
        q_id: item.q_id,
        question: item.question,
        question_id: item.question_id,
        choice: []
      };
    }
    acc[key].choice.push(item.choice);
    return acc;
  }, {}));
};

// 修改查询函数，加入数据处理
const getQuestionAndChoices = async (req, res) => {
  try {
    // 修正SQL中的表名错误
    const getAllData = await pool.query(
        'select * from questions join choices on questions.q_id = choices.question_id');
    const groupedData = groupQuestionsWithChoices(getAllData.rows);
    return res.status(200).json(groupedData);
  } catch (error) {
    return res.status(400).json(error.message);
  }
};

方案二：PostgreSQL聚合查询（推荐） #

直接通过SQL的ARRAY_AGG函数聚合选项，数据库返回的结果直接符合预期格式，减少数据传输与后端处理量：

const getQuestionAndChoices = async (req, res) => {
  try {
    const getAllData = await pool.query(`
      SELECT 
        q.q_id,
        q.question,
        q.q_id AS question_id,
        ARRAY_AGG(c.choice) AS choice
      FROM questions q
      JOIN choices c ON q.q_id = c.question_id
      GROUP BY q.q_id, q.question;
    `);
    return res.status(200).json(getAllData.rows);
  } catch (error) {
    return res.status(400).json(error.message);
  }
};

内容的提问来源于stack exchange，提问作者Muhammad Abdul Rauf