PostgreSQL – Subquery to Return Multiple Columns or JSON Object

postgresql

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"

Best Answer

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);
Related Question