Interleaving array_agg in postgres

Question

I have a postgres query in which I want to interleave my array_agg statements :

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

Gives me the following:

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

I would like the results to be:

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

How do I do this?

McNets · Accepted Answer · 2017-06-05 15:14:14Z

3

I've set up a small example similar to yours:

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

user_id | array_agg                             
------: | :-------------------------------------
      1 | {"question 1,1,yes","question 2,2,no"}
      2 | {"question 1,1,no","question 2,2,yes"}

dbfiddle here

answered Jun 5, 2017 at 15:14

McNets

10.9k3 gold badges39 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jonathan Eustace Over a year ago

Thank you! Better then my solution for sure.

McNets Over a year ago

I'm glad to help.

Jonathan Eustace · Accepted Answer · 2017-06-05 15:13:03Z

1

To interleave or splice together multiple array_agg's you can do the following:

SELECT client_user_id, 
   array_agg('[' || client_intake_question_id || question || ',' || 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

answered Jun 5, 2017 at 15:13

Jonathan Eustace

2,49513 gold badges35 silver badges54 bronze badges

Collectives™ on Stack Overflow

Interleaving array_agg in postgres

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related