CODEWITHSUNDEEP
postgresqlaggregate-functions
MiKlacko
MiKlacko

Reputation: 81

Postgres json_agg Limit

I'm using json_agg in Postgres like this

json_agg((e."name",e."someOtherColum",e."createdAt") order by e."createdAt" DESC )

But I want to limit how many rows will be aggregated into JSON. I want to write something like this

json_agg((e."name",e."someOtherColum",e."createdAt") order by e."createdAt" DESC  LIMIT 3)

Is it possible in some way?

This is full query

SELECT e."departmentId",
json_agg((e."name",e."someOtherColum",e."createdAt") order by e."createdAt" DESC ) as "employeeJSON" 
FROM "Employee" e 
GROUP BY e."departmentId"

So I want to achieve department with first three employees for each department.

Upvotes: 4

Views: 6453

Answers (1)

user330315
user330315

Reputation:

You need a sub-select that returns only three rows per departmentid and then aggregate the result of that:

select "departmentId", 
       json_agg(("name","someOtherColum","createdAt") order by "createdAt" DESC) as "employeeJSON"
FROM (
  SELECT "departmentId",
         "name"
         "someOtherColum",
         "createdAt", 
         row_number() over (partition by "departmentId" order by "createdAt") as rn
  FROM "Employee"
) t
WHERE rn <= 3
GROUP BY "departmentId"

Note that using quoted identifiers is in general not such a good idea. In the long run it's more trouble than they are worth it.

Upvotes: 6

Related Questions