Subquery to return multiple columns or a JSON object

Question

Is there any way for me to return multiple columns from a subquery or at least return a column that is a JSON object of all of the columns I need?

This is a working query that works:

select r.*,
 (select status
 from builds
 where repo = r.id
 and branch = 'master'
 order by id desc
 limit 1)
from repos r
where id = any(1ドル)

Where 1ドル is an array of ids

But I would like to return more than just the status column from the builds table:

select r.*,
 (select status, start_time, end_time
 from builds
 where repo = r.id
 and branch = 'master'
 order by id desc
 limit 1)
from repos r
where id = any(1ドル)

After looking around it seems that row_to_json should work for me. However, I'm not sure how it should work in this given case:

select r.*,
 row_to_json(select status,
 start_time, end_time
 from builds
 where repo = r.id
 and branch = 'master'
 order by id desc
 limit 1) as build
from repos r
where id = any(1ドル)

I am getting

syntax error at or near "select"

Answer 1

Don't make life hard for yourself, use a lateral join:

SELECT r.*, 
 b.status, b.start_time, b.end_time
FROM repos AS r
 LEFT JOIN LATERAL
 (SELECT status, start_time, end_time
 FROM builds
 WHERE repo = r.id
 AND branch = 'master'
 ORDER BY id DESC
 LIMIT 1
 ) AS b ON TRUE
WHERE id = ANY(1ドル);

• rinomau: Wow, this is an awesome solution !!!