Aggregating rows into JSON hash

Question

I have the following table:

roles

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

Where name can vary to any value.

I'd like to get a JSON hash like so:

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

Using PL/PGSQL

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

I'm gonna assume the name's can vary cause without it it's no challenge. Usins the 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

We can create a dynamic crosstab:

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');

EDIT

Less fancy aproach:

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

edited May 23 '17 at 11:50

Community

1
1

answered Apr 20 '13 at 01:05

Jakub Kania

15,665
2
37
47

This is an interesting approach, I'll see what I can do with it. – Nicolas Goy Apr 20 '13 at 06:31
I tried your approach and it works, but performances are terrible. I am currently investigating a way to improve app performance (JSON generation is in ruby right now), I should have mentioned performances requirements. I will still accept your answer as it does answer my question. – Nicolas Goy Apr 20 '13 at 12:57
@NicolasGoy I think I may have gotten a bit carried away with that crosstab. The query I added now should perform much better. – Jakub Kania Apr 20 '13 at 13:56
I'll look at what 9.3 proposes, but your solution still works for now. – Nicolas Goy Jul 20 '13 at 23:13

score 1 · Answer 2 · answered Apr 21 '13 at 22:46

The current development version - future 9.3 - of postgres will put a strong focus on JSON support inside the database. Depending on how fast you need a solution for this problem you could wait for 9.3 or install the current beta (which should include the new features).

Here are two articles regarding the new json features - first is third party - second is the JSON lib reference on the dev tree:

Aggregating rows into JSON hash

2 Answers2

EDIT