NODE JS: grouping data from join table-CodePudding

I have two tables that I join using PostgreSQL for my quiz app. After retrieving the join table, the data does not look like what I expected.
Is there any way I can group the data with JavaScript function?

Here is my table:

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

  CREATE TABLE choices (
    c_id BIGSERIAL PRIMARY KEY,
    choice text,
    question_id BIGINT REFERENCES test1 (q_id)
  )

my code to retrieve the data from join table:

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

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

the result:

[
    {
        "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"
    }
]

what I expected:

[
    {
        "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"
    },
]

CodePudding user response：

You can try to aggregate choice right in the SQL like this:

select q_id, question, question_id, array_agg(choice) as choice
from questions join choices on questions.q_id = choices.question_id
group by q_id, question, question_id
order by q_id, question, question_id

(I also replaced answers with choices)