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

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?

• Sirjon: This works too. Thank you. Performance-wise, how efficient is this query?
• Erwin Brandstetter: @Sirjon: What do you mean "too"? The currently accepted answer cannot work at all for your purpose. It picks rows uncorrelated to the main filter WHERE t.Id = 3. (Well, if 3 happens to be your smallest t.id, that query may appear to work for this particular filter.) This query is as efficient as it gets. The right indexes on joining and filtering columns are key to performance, plus a multicolumn index that supports your undisclosed ORDER BY in table articles.
• Sirjon: if I want to get an estimate of total row count before limit/offset, how can I combine that with this query? I have seen your answer but that's too costly and I don't want exact total count.

Answer 2

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

• Erwin Brandstetter: @Sirjon: This can't be the solution to your question.
• Sirjon: @ErwinBrandstetter It's not.