SQL Alias Column From Parent Query-CodePudding

To simplify the problem I have here are two tables:

subjects:

id - INT
device_id - varchar

reports:

id
created_on
start_time
end_time
subject_ids - array of subject ids

I would like a postgres statement that would return to me all columns from the reports table and add an extra column of an array of device_id from the subject table based on the array of subject_ids from the reports table.

The example is simpler than the total schema and so changing the schema is not an option here.

Thanks

CodePudding user response：

You'll have to first UNNEST() your subject_ids so you get individual rows from your reports table for each subject_id. Then join to your subjects table and ARRAY_AGG() your device_id.

It will look something like:

SELECT r.id, ARRAY_AGG(s.device_id)
FROM (SELECT id, UNNEST(subject_ids) as subject_id FROM reports) r
   LEFT OUTER JOIN subjects s ON r.subject_id = s.id
GROUP BY r.id;

Here is a DBFiddle of this in action