postgresqljoinlateral

Postgres Lateral Join Multiple Tables to Limit Results


I have a question regarding lateral joins in Postgres.

My use case is I want to return a dataset that combines multiple tables but limits the number of publications and reviews returned. The simplified table schema is below

Table Author

Table Review

Table Publication

Table AuthorPublication

So for my initial query I have this:

SELECT
  a.id,
  a.name
    json_agg (
      json_build_object (
        'id', r.id,
        'content', r.content 
       )
    ) AS reviews,
    json_agg (
      json_build_object(
        'id', p.id, 
        'name', p.name
        )
    ) AS publications
FROM
  public.author a
INNER JOIN
  public.review r ON r.author_id = a.id
INNER JOIN
  public.author_publication ap ON ap.author_id = a.id 
INNER JOIN 
  public.publication p ON p.id = ap.publication_id
WHERE 
  a.id = '1'
GROUP BY
  a.id

This returns the data I need, for example I get the author's name, id and a list of all of their reviews and publications they belong to. What I want to be able to do is limit the number of reviews and publications. For example return 5 reviews, and 3 publications.

I tried doing this with a lateral query but am running into an issue where if I do a single lateral query it works as intended.

so like:

INNER JOIN LATERAL
 (SELECT r.* FROM public.review r WHERE r.author_id = a.id LIMIT 5) r ON TRUE

This returns the dataset with only 5 reviews - but if I add a second lateral query

INNER JOIN LATERAL
 (SELECT ap.* FROM public.author_publication ap WHERE ap.author_id = a.id LIMIT 5) r ON TRUE

I now get 25 results for both reviews and publications with repeated/duplicated data.

So my question is are you allowed to have multiple lateral joins in a single PG query and if not what is a good way to go about limiting the number of results from a JOIN?

Thanks!


Solution

  • You must change your query to something like this:

    SELECT
      a.id,
      a.name,
      (
        SELECT
            json_agg ( r )
          FROM (
                 SELECT 
                      json_build_object (
                          'id', r.id,
                          'content', r.content 
                      ) AS r
                   FROM public.review r 
                  WHERE r.author_id = a.id
                  ORDER BY r.id
                  LIMIT 5
               ) AS a
      ) AS reviews,
      (
       SELECT
            json_agg (p)
         FROM (
                SELECT
                     json_build_object(
                        'id', p.id, 
                         'name', p.name
                     ) AS p
                  FROM public.author_publication ap 
                 INNER JOIN public.publication p ON p.id = ap.publication_id
                 WHERE ap.author_id = a.id
                 ORDER BY p.id
                 LIMIT 3
            ) AS a
       ) AS publications
    FROM
      public.author a
    WHERE 
      a.id = '1'