PostgreSQL JSON聚合函数

Question

我正在使用Postgres 9.4，我有如下查询：

SELECT pid, code, name FROM activity, (
  SELECT code FROM project
) projects WHERE activity.pcode = projects.code;

返回以下关系：

pid | code |    name    
-------------------------------
  1 | p1   | activity1 
  1 | p3   | activity2
  2 | p1   | activity3
  2 | p2   | activity4
  2 | p3   | activity5

我正在尝试编写相同的查询，但希望获得与每个"pid"相关的项目代码和活动名称的json数组。

因此，我正在寻找一个查询，可以返回类似于以下内容：

pid | json    
------------------------------------------------------------------------------------
  1 | [{'code': 'p1', 'name': 'activity1'}, {'code': 'p3', 'name': 'activity2'}] 
  2 | [{'code': 'p1', 'name': 'activity3'}, {'code': 'p2', 'name': 'activity4'}, {'code': 'p3', 'name': 'activity5'}] 

有什么想法吗？ 非常感谢您的帮助。

更新

这是我所做的（接近于Abelisto所说的）：

SELECT pid, json_agg(json_build_object('code', code, 'name', name)) AS agg                                                                                                                                    
FROM activity JOIN (                                                                                                                                                                                          
  SELECT code FROM project                                                                                                                                                                                    
) AS p ON p.code=activity.pcode                                                                                                                                                                               
GROUP BY pid;    

- Mikou

---

你反对将此作为脚本的一部分来完成吗？比如在Python中？你可以创建一个服务器端游标并遍历记录以减少性能问题。 - codeBarer

@codeBarer 我知道已经过去五年了。没有反对你的建议（在Python脚本中进行此转换-我个人会在Python中做类似的事情）。但是我很好奇原因。你认为用Python做会更好吗？我问这个问题是因为到目前为止，我的印象是在数据库引擎中执行比在脚本语言中执行要快得多。您能详细说明一下您对这两种方法的利弊吗？ - m01010011

Answer 1

select
  pid,
  json_agg(json_build_object('code',code,'name',name))
from
  ...
group by
  pid

- Abelisto

---

那是一个非常棒的答案！ - MC Hammerabi

Answer 2

SELECT pid from activity a, (
    select json_agg(project) from project where code = a.code 
) as p

类似这样的内容...

- ceadreak