/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

1 vote315 views1 answer
Mysql show data from table
SQL
0 vote354 views1 answer
Sql get year from datetime field
SQL
0 vote299 views1 answer
Sql requete number pair
SQL
0 vote358 views1 answer
How to select all fieldsin a soql query
SQL
0 vote353 views1 answer
Postgresql check total storage
SQL
0 vote338 views1 answer
Create table in mysql mariadb primary key foreign key
SQL
0 vote331 views2 answers
Sql query in dax power bi
SQL
1 vote357 views2 answers
Django.core.exceptions.ImproperlyConfigured: mysqlclient 1.4.0 or newer is required; you have 0.10.1.
SQL
0 vote314 views1 answer
Oracle execute immediate quotes
SQL
10 votes321 views4 answers
Fetch first 5 characters of the string in sql
SQL