Aggregate with a limit in postgres

Question

In this query, each row of table a could have hundreds of rows of table b associated with it. So the array_agg contains all of those values. I'd like to be able to set a limit for it, but instide array_agg I can do order by but there's no way to set a limit.

select a.column1, array_agg(b.column2)
from a left join b using (id)
where a.column3 = 'value'
group by a.column1

I could use the "slice" syntax on the array but that's quite expensive since it first has to retrieve all the rows then discard the other ones. What's the proper efficient way to do this?

Answer 1

I would use a lateral join.

select a.column1, array_agg(b.column2)
from a left join lateral 
    (select id, column2 from b where b.id=a.id order by something limit 10) b using (id)
where a.column3 = 'value'
group by a.column1

Since the "id" restriction is already inside the lateral query, you could make the join condition on true rather than using (id). I don't know which is less confusing.