sql - 使用 json 格式的 array_agg 检索一对多关系行 - postgresql

Question

我有两个表:1.面试2.问题

每个面试表可以多出一个问题。

我想以这样的方式检索数据，即在一行中显示采访详细信息以及所有问题及其详细信息，在一个列中，与数组格式的特定采访相关联。

我已经用 array_agg()、json_object_build() 尝试了所有方法，但似乎无法使其正常工作。

模式: Table Schema Image

我现在拥有的 SQL 查询:

SELECT
         i.id as interview_id, i.board, i.time_taken, i.notes, i.interview_date, 
         u.id as user_id, u.first_name, u.last_name, u.state, u.district, u.optional, 
         j.name as job, 
         json_build_object('question', q.question, 'answer', q.answer, 'member', q."member", 'order', q."order") as questions
         FROM interview i
         LEFT JOIN question q ON q.interview_id = i.id 
         INNER JOIN users u ON i.user_id = u.id 
         INNER JOIN user_jobs uj ON uj.user_id = u.id 
         INNER JOIN job j ON uj.job_id = j.id
         GROUP BY u.id, i.id, j.name, q.question, q.answer, q.order, q."member";

我得到的结果:

interview_id | time | ... | questions
1001         | 25   | ... | {"question": "How are you", "answer": "I'm good", ...}
1001         | 25   | ... | {"question": "What's your name", "answer": "My name is..", ...}
1002         | 40   | ... | {"question": "Who are you", "answer": "I'm nobody", ...}
1002         | 40   | ... | {"question": "Are you a robot", "answer": "No, I'm not", ...}

我想合并具有相同 interview_id 的行并将问题合并到一个 json 对象数组中。在 json_build_object() 周围使用 array_agg() 也没有用。

我想要的结果:

interview_id | time | ... | questions
1001         | 25   | ... | [{"question": "...", "answer": "...", ...}, {"question": "...", "answer": "...", ...}]
1002         | 40   | ... | [{"question": "...", "answer": "...", ...}, {"question": "...", "answer": "...", ...}]

在检索采访 id 后单独查询问题表是否可能或更好？

数据库:PostgreSQL
环境:Node.js - Express(node-postgres 包)

非常感谢您的帮助。请询问是否需要更多详细信息。

Answer 1

您需要修复您的 GROUP BY 并使用 JSON_AGG():

SELECT i.id as interview_id, i.board, i.time_taken, i.notes, i.interview_date, 
       u.id as user_id, u.first_name, u.last_name, u.state, u.district, u.optional, 
       j.name as job, 
       json_agg(json_build_object('question', q.question, 'answer', q.answer, 'member', q."member", 'order', q."order")) as questions
FROM interview i LEFT JOIN
     question q
     ON q.interview_id = i.id JOIN
     users u
     ON i.user_id = u.id JOIN
     user_jobs uj
     ON uj.user_id = u.id JOIN
     job j
     ON uj.job_id = j.id
GROUP BY u.id, i.id, j.name