/SQL/Nodejs sql get one to many relationship as array

Nodejs sql get one to many relationship as array

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

Source: stackoverflow.com

Similar codes

0 vote575 views1 answer
Power bi connect to postgresql
SQL
27 votes284 views4 answers
Get all tables with column name sql
SQL
2 votes302 views1 answer
Datetrunc
SQL
0 vote307 views1 answer
Postgres dump sql insert
SQL
0 vote294 views2 answers
Oracle sql for each row
SQL
20 votes280 views4 answers
Reset auto increment in MySQL
SQL
0 vote335 views3 answers
Mysql CAST(amount as float)
SQL
0 vote348 views2 answers
Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels. Your result cannot contain duplicates.
SQL
28 votes266 views4 answers
Mysql delete database
SQL
1 vote264 views1 answer
Mysql select row with max value group by
SQL