将行聚合到JSON散列中

Question

我有下表：

roles

id |  name | person
---+-------+--------
1  | admin | jon
2  | admin | fred
3  | user  | alfred
4  | user  | jon

其中名称可以更改为任何值。

我想得到一个JSON散列，如下所示：

{"admin": ["jon", "fred"], "user": ["alfred", "jon"]}

使用PL/PGSQL

Answer 1

我假设它的名字是不同的，因为没有它就不会有任何挑战。使用eval function

CREATE OR REPLACE FUNCTION eval(expression text) RETURNS text
AS
$body$
DECLARE
  result text;
BEGIN
  execute expression INTO RESULT;
  RETURN result;
END;
$body$
language plpgsql

我们可以创建一个动态交叉表：

SELECT eval('SELECT row_to_json(q)
FROM (SELECT '||(SELECT string_agg(DISTINCT '"'||name||'"',',')
                 FROM roles)||'
      FROM crosstab(''SELECT 1,name,array_agg(person)::text[]
                      FROM roles
                      GROUP BY name;
                    '') as ct(row_name int,'||(SELECT string_agg(DISTINCT '"'||name||'"                   text[]',',')
FROM roles)||')
)as q');

编辑

不那么花哨的方法：

SELECT '{'||string_agg(temp,',')||'}'
FROM
(
SELECT '"'||name||'": ['||(string_agg(person,',')::text)||']' as temp FROM roles GROUP BY name
) as q

Answer 2

postgres当前的开发版本-未来的9.3 -将把重点放在数据库内部的JSON支持上。根据你需要这个问题的解决方案的速度，你可以等待9.3或安装当前的测试版(应该包括新功能)。

这里有两篇关于json新特性的文章--第一篇是第三方，第二篇是开发树上的JSON lib引用：

• postgres 9.3 features highligh json
• postgres devel json lib