postgresql - Postgres json_agg 限制

Question

我在 Postgres 中像这样使用 json_agg

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

但我想限制将多少行聚合到 JSON 中。我想写这样的东西

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

这在某种程度上是可能的吗？

这是完整的查询

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"

所以我想实现每个部门都有前三名员工的部门。

Answer 1

您需要一个子选择，每个 departmentid 只返回三行，然后聚合结果:

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"

---

^{请注意，使用带引号的标识符通常不是一个好主意。从长远来看，这比他们值得的麻烦更多。}