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
Advertisement
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.