Postgres, how to limit number of rows returned from joined tables

I have the following query that return the data I want, however for the joined tables, I want to limit the number of rows returned and preferrably be able to specify for each joined table. I tried using limit with the select itself, but doesn’t seem to be supported.

Is this possible? I am using Postgres 11.

select array_to_json(array_agg(t)) from (
        select 
        tbl_327.field_43,tbl_327.field_1,tbl_327.field_2,
        jsonb_agg(distinct jsonb_build_object('id',tbl_332.id,'data',tbl_332.fullname)) as field_7,
        jsonb_agg(distinct jsonb_build_object('id',tbl_312.id,'data',tbl_312.fullname)) as field_33
        from schema_1.tbl_327 tbl_327
        left join schema_1.tbl_327_to_tbl_332_field_7 field_7 on field_7.tbl_327_id=tbl_327.id
            left join schema_1.tbl_332_customid tbl_332 on tbl_332.id = field_7.tbl_332_id
            left join schema_1.tbl_327_to_tbl_312_field_33 field_33 on field_33.tbl_327_id=tbl_327.id
            left join schema_1.tbl_312_customid tbl_312 on tbl_312.id = field_33.tbl_312_id
            group by tbl_327.field_43,tbl_327.field_1,tbl_327.field_2
        ) t

UPDATED here is my new query. I simplified it, but the issue is it’s no longer returning correct data. For the field_4 field, it’s returing rows/data that isn’t associated with the record. Do I have something wrong?

select array_to_json(array_agg(t)) from (
    select 
    tbl_342.field_1,tbl_342.field_2,tbl_342.id,
    jsonb_agg(distinct jsonb_build_object('id',tbl_312.id,'data',tbl_312.fullname)) as field_4
    from schema_1.tbl_342 tbl_342
    left join lateral (
            select distinct field_4.*
            from  schema_1.tbl_342_to_tbl_312_field_4 field_4
            where field_4.tbl_342_id=tbl_342.id
            limit 50) field_4 on true
            
            left join lateral (
                select distinct tbl_312.*
                from schema_1.tbl_312_customid tbl_312
                where tbl_312_id = field_4.tbl_312_id
                limit 5
            ) tbl_312 on true

    group by tbl_342.field_1,tbl_342.field_2,tbl_342.id
    ) t

Answer

One approach is to turn each left join to a lateral join; you can then set the limit within each subquery:

select array_to_json(array_agg(t)) from (
    select 
        tbl_327.field_43,tbl_327.field_1,tbl_327.field_2,
        jsonb_agg(distinct jsonb_build_object('id',tbl_332.id,'data',tbl_332.fullname)) as field_7,
        jsonb_agg(distinct jsonb_build_object('id',tbl_312.id,'data',tbl_312.fullname)) as field_33
    from schema_1.tbl_327 tbl_327
    left join lateral (
        select field_7.*
        from schema_1.tbl_327_to_tbl_332_field_7 field_7 
        where field_7.tbl_327_id=tbl_327.id
        order by ...
        limit 5
    ) field_7 on true
    left join lateral (
        select tbl_332.*
        from schema_1.tbl_332_customid tbl_332 
        where tbl_332.id = field_7.tbl_332_id
        order by ??
        limit 5
    ) tbl_332 on true
    left join lateral ...
    group by tbl_327.field_43,tbl_327.field_1,tbl_327.field_2
) t

Note that you need an order by to go along with limit in order to get stable results – you can replace the question marks in the query with the revelant columns or set of columns.