Using limit in array_agg in Postgres

Question

I am fetching user's tasks as an array using array_agg function in postgres but I want to limit it to latest 10 tasks. I am unable to use LIMIT in subquery. I also tried to use row_number function but it throws error that window functions can not be used in WHERE.

Below is the query I am using.

SELECT 
  *, 
  COUNT(*) OVER () as count 
FROM 
  (
    SELECT 
      *, 
      (
        SELECT 
          ARRAY_AGG(
            JSON_BUILD_OBJECT(
              'id', taskhistories.id, 'task', taskhistories.task, 
              'taskname', tasks.name, 'project', 
              taskhistories.project, 'projectname', 
              projects.name, 'started_at', taskhistories.started_at, 
              'stopped_at', taskhistories.stopped_at
            )
          ) as tasks 
        FROM 
          taskhistories 
          LEFT JOIN tasks ON taskhistories.task = tasks.id 
          LEFT JOIN projects ON taskhistories.project = projects.id 
        WHERE 
          users.id = taskhistories.user 
        -- LIMIT 10  this is something I want to achieve
      ) AS tasks 
    FROM 
      users 
    WHERE 
      isadmin IS NOT TRUE
  ) as users 
LIMIT 
  10 OFFSET 0

Schema Queries

@user11666461 nope, this does not work I already tried it but don't know why it does not apply limit on tasks array. — Drashti Kheni
– Drashti Kheni, Commented Jan 10, 2023 at 5:07
What is the error message? Please edit the question and add it there. — Laurenz Albe
– Laurenz Albe, Commented Jan 10, 2023 at 6:44
@LaurenzAlbe I have already mentioned the error message that window functions can not be used in WHERE. — Drashti Kheni
– Drashti Kheni, Commented Jan 10, 2023 at 6:46
LIMIT is applied after aggregation, so that won't work anyway if you are trying to limit the size of the array. Try another subquery layer. — Laurenz Albe
– Laurenz Albe, Commented Jan 10, 2023 at 6:56
@LaurenzAlbe Sorry for bothering you. Can you please add demo code? because I tried my best before adding the question here but did not get any solution. — Drashti Kheni
– Drashti Kheni, Commented Jan 10, 2023 at 6:57

Laurenz Albe · Accepted Answer · 2023-01-10 07:16:47Z

1

LIMIT is executed after aggregation, so that won't work anyway. Try something like this in your subquery:

SELECT JSONB_AGG(t.task) as tasks 
FROM (SELECT JSONB_BUILD_OBJECT(
                'id', taskhistories.id,
                'task', taskhistories.task, 
                'taskname', tasks.name,
                'project', taskhistories.project,
                'projectname', projects.name,
                'started_at', taskhistories.started_at, 
                'stopped_at', taskhistories.stopped_at
             ) AS task
      FROM taskhistories 
          LEFT JOIN tasks ON taskhistories.task = tasks.id 
          LEFT JOIN projects ON taskhistories.project = projects.id 
      WHERE users.id = taskhistories.user
      LIMIT 10) AS t

answered Jan 10, 2023 at 7:16

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Laurenz Albe Over a year ago

Thanks. I checked with the schema you provided, and both my subquery and your original query (with the LIMIT) ran without an error.

Collectives™ on Stack Overflow

Using limit in array_agg in Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related