CODEWITHSUNDEEP
sqlpostgresqljoinsubquery
No Such Agency
No Such Agency

Reputation: 79

SQL Alias Column From Parent Query

To simplify the problem I have here are two tables:

subjects:

reports:

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

Upvotes: 0

Views: 104

Answers (1)

JNevill
JNevill

Reputation: 50200

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

Upvotes: 1

Related Questions