How to use LIMIT and OFFSET in Postgres' json_agg() function

Question

With reference to this question and this SQL Fiddle, I currently have a working SQL query, based off of this answer, that gets a tag from the database and aggregates all articles having such a tag. The current query is:

SELECT
    t.id AS t_id,
    t.name AS t_name,
    t.description AS t_description,
    json_agg(
    jsonb_build_object(
        'id', a.id,
        'fore_image', a.fore_image,
        'title', a.title,
        'slug', a.slug,
        'content', a.content,
        'is_published', a.is_published,
        'created_at', a.created_at,
        'updated_at', a.updated_at, 
        'author', jsonb_build_object(
          'id', u.id,
          'email', u.email,
          'first_name', u.first_name,
          'last_name', u.last_name,
          'is_active', u.is_active,
          'is_staff', u.is_staff,
          'is_superuser', u.is_superuser
        )
    )) AS articles
FROM tags AS t
JOIN articles_tags AS a_t ON a_t.tag_id = t.id
JOIN articles AS a ON a_t.article_id = a.id
JOIN users AS u ON a.user_id = u.id
WHERE t.Id = 3
GROUP BY t.id;

But at present, json_agg() is fetching all related articles at once which will be inefficient in the long run. Is there a way I can introduce LIMIT and OFFSET to json_agg() so I can control the number of articles a query produces?

Answer 1

It seems like you want to limit the number of articles, so you could put that LIMIT and OFFSET into a subquery on that table. Instead of

JOIN articles AS a

write

JOIN (SELECT id, fore_image, title, slug, content,
             is_published, created_at, updated_at, user_id
      FROM articles
      ORDER BY id /* or something else */
      OFFSET 0 LIMIT 50) AS a

Answer 2

You need to aggregate in a LATERAL subquery, where you apply LIMIT, and (optionally) OFFSET and ORDER BY. So:

LEFT JOIN LATERAL (<magic happens here>) alias ON true

A plain JOIN without LATERAL wouldn't work.
LEFT JOIN to report the tag even if no articles were found. Else switch to CROSS JOIN.

While being at it, and while you report all keys with their original column names, you can use shorter syntax, too:

SELECT t.id, t.name, t.description, atu.articles
FROM   tags t
LEFT   JOIN LATERAL (
   SELECT json_agg(article) AS articles
   FROM  (
      SELECT a.id, a.fore_image, a.title, a.slug, a.content
           , a.is_published, a.created_at, a.updated_at
           ,(SELECT author
             FROM  (
                SELECT u.id, u.email, u.first_name, u.last_name
                     , u.is_active, u.is_staff, u.is_superuser
                FROM   users u
                WHERE  u.id = a.user_id
                ) author
            )
      FROM   articles_tags a_t
      JOIN   articles      a ON a.id = a_t.article_id
      WHERE  a_t.tag_id = t.id
      ORDER  BY a.slug, a.id  -- or whatever
      OFFSET 0
      LIMIT  123
      ) article
   ) atu ON true
WHERE  t.Id = 3;

To report all columns of the tables articles and users, simplify further:

SELECT t.id, t.name, t.description, atu.articles
FROM   tags t
LEFT   JOIN LATERAL (
   SELECT json_agg(article) AS articles
   FROM  (
      SELECT a.*, (SELECT author FROM users AS author WHERE author.id = a.user_id)
      FROM   articles_tags a_t
      JOIN   articles      a ON a.id = a_t.article_id
      WHERE  a_t.tag_id = t.id
      ORDER  BY a.slug, a.id  -- or whatever
      OFFSET 0
      LIMIT  123
      ) article
   ) atu ON true
WHERE  t.Id = 3;

See:

• Return multiple columns of the same row as JSON array of objects
• Using row_to_json() with nested joins
• What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?