How to group data in postgresql?

Question

PostgreSQL

I have two tables (one for portfolios, one for portfolio photos), how do I output an array of portfolios with their associated photos? How to build a query for SELECT?

[{ 
  name: '', descripton: '',
  ... 
  images: [{src: 'url', src_build: 'url'}, {src: 'url', src_build: 'url'}]
}]

Answer 1

select 
  portfolio.id, portfolio.name,
  array_agg(json_build_object('src', photos.src, 'src_build', photos.src_build))
from portfolio
left join photos on portfolio.id = photos.portfolio_id
group by portfolio.id, portfolio.name
;

PostgreSQL fiddle