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

CodePudding user response：

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