1

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?

2 Answers 2

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:

3
  • This works too. Thank you. Performance-wise, how efficient is this query?
    – Sirjon
    Feb 23, 2023 at 2:16
  • @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. Feb 23, 2023 at 3:28
  • 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.
    – Sirjon
    Feb 23, 2023 at 10:16
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
2
  • 1
    @Sirjon: This can't be the solution to your question. Feb 22, 2023 at 23:11
  • @ErwinBrandstetter It's not.
    – Sirjon
    May 9, 2023 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.