postgresql - 在postgres中交错array_agg

我有一个postgres查询，在该查询中，我想交叉使用数组agg语句：

SELECT client_user_id,
       (array_agg(question), array_agg(client_intake_question_id), array_agg(answer)) as answer
FROM client_intake_answer
LEFT OUTER JOIN client_intake_question
ON       client_intake_question.id = client_user_id
GROUP BY client_user_id

给我以下信息：

 5 | ("{""Have you ever received counselling?"",""Have you ever received counselling or mental health support in the past?""}","{1,2}","{yes,no}")

我希望结果是：

  5 | ("{""Have you ever received counselling?", 1, "yes"",""Have you ever received counselling or mental health support in the past?", 2, "no""}"

我该怎么做？

最佳答案

我举了一个和你类似的小例子：

create table answers(user_id int, question_id int, answer varchar(20));
create table questions(question_id int, question varchar(20));

insert into questions values
(1, 'question 1'),
(2, 'question 2');

insert into answers values
(1, 1, 'yes'),
(1, 2, 'no'),
(2, 1, 'no'),
(2, 2, 'yes');

select user_id, array_agg(concat(questions.question, ',',  questions.question_id::text, ',', answers.answer))
from   questions
inner join answers
on     questions.question_id = answers.question_id
group by answers.user_id

用户| id |数组_agg
------: | :-------------------------------------
1 |{“问题1，1，是”，“问题2，2，否”}
2 |{“问题1，1，否”，“问题2，2，是”}
双小提琴here

关于postgresql - 在postgres中交错array_agg，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44371726/