我有兩個表，我使用 PostgreSQL 加入我的測驗應用程式。檢索連接表后，資料看起來不像我預期的那樣。
有什么方法可以使用 JavaScript 函式對資料進行分組？
這是我的桌子：
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)
)
我從連接表中檢索資料的代碼：
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)
}
}
結果：
[
{
"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"
},
]
uj5u.com熱心網友回復：
您可以嘗試choice在 SQL 中進行聚合，如下所示：
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
（我也替換answers為choices）
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/514690.html