Retrieve One to Many Relationship Rows using array_agg in json format - postgresql

Question

I have a two tables called: 1. Interviews 2. Questions

Each Interview table can have one more more questions.

I want to retrieve data in such way that, in a single row, the interview details are present as well as all the questions and its details, in a single column, associated with that particular interview in an array format.

I have tried everything with array_agg(), json_object_build() but can't seem to make it work exactly.

Schemas: Table Schema Image

SQL Query I have now:

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";

The result I get:

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", ...}

I want to combine the rows having same interview_id and merge the questions into an array of json objects. Using array_agg() around json_build_object() is of no use too.

The result I want:

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

Is it possible or is it better to query the questions table separately after retrieving the interview ids?

Database: PostgreSQL
Environment: Node.js - Express (node-postgres package)

Thanks a lot for any help. Please ask if more details are needed.

For a very simple example see db-fiddle.com/f/gyHBwM7NBGXZNJv3Cat8ZN/0 — 404
– 404, Commented Dec 4, 2019 at 10:17

Gordon Linoff · Accepted Answer · 2019-12-04 12:21:14Z

7

You need to fix your GROUP BY and use 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

answered Dec 4, 2019 at 12:21

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

Sainath A Over a year ago

Works perfectly! Thank you very much.

Collectives™ on Stack Overflow

Retrieve One to Many Relationship Rows using array_agg in json format - postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related