Postgres json_agg Limit

Question

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.

pozs · Accepted Answer · 2015-02-09 10:50:47Z

6

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.}

edited Feb 9, 2015 at 10:50

pozs

36.6k5 gold badges61 silver badges66 bronze badges

answered Feb 8, 2015 at 12:49

user330315

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

Collectives™ on Stack Overflow

Postgres json_agg Limit

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related