PostgreSQL的JSON聚合 [英] postgresql json aggregate

问题描述

我使用的是Postgres 9.4，我有以下查询:

I'm using postgres 9.4, I have the following query:

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数组获得

I am trying to write the same query but so that I get the project code an the activity name as a json Array relative to each "pid"

所以我正在寻找一个查询，该查询将返回如下内容:

So I am looking for a query that would return something like this:

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

有什么想法吗? 预先感谢您的帮助

Any ideas? Thanks in advance for the help

更新

这是我所做的(与Abelisto所说的很接近):

Here is what I did (which is close to what Abelisto said):

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;    

推荐答案

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

这篇关于PostgreSQL的JSON聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！