sql - POSTGRESQL 如何将子表的行插入到父表的数组中

Question

我有 2 个相关表:问题和答案

问题

CREATE TABLE questions (
    id SERIAL PRIMARY KEY,
    title TEXT,
    answer_ids INTEGER[]
);

答案

CREATE TABLE answers (
    answer_id SERIAL PRIMARY KEY,
    question_id INTEGER
        REFERENCES questions,
    body TEXT
);

当前实现:

当向答案表中添加一行时，我会将答案的 answer_id 推送到适当行中问题表的 answer_ids 数组。即

如果对 id = 1 的问题添加了 answer_id = 5 的答案。我会做类似的事情

UPDATE questions SET answer_ids = ARRAY_APPEND(answer_ids, 5) WHERE id = 1;

我想要实现的目标:

我不一定要寻找一种方法将答案添加到问题表中，因为我觉得这会是多余的，那么拥有答案表的意义何在？

我正在寻找一个可以让我得到问题的查询，但我想要的不是 answer_ids 数组，而是 answers 本身的数组。所以查询会在 JSON 中返回类似这样的内容:

{ id: 1,
  title: 'title of question 1',
  answers: [{ answer_id: 1,
              question_id: 1,
              body: 'body of answer 1'},
            { answer_id: 3,
              question_id: 1,
              body: 'body of answer 3'}]
}

Answer 1

可能是这个？ ( here are functions for work with json )

WITH Questions(id, title) as (
    select 1, 'title 1' union all
    select 2, 'title 2' 
),
Answers(answer_id, question_id, body) as (
    select 1, 1, 'body of answer 1' union all
    select 3, 1, 'body of answer 3'  union all
    select 4, 2, 'body of answer 4'  
)

SELECT json_build_object('id',id, 'title',title, 'answers', 
    (SELECT json_agg(json_build_object('answer_id',answer_id,'question_id',question_id,'body',body)) FROM Answers WHERE Answers.question_id = Questions.id) ) 
from Questions 
WHERE Questions.id = 1