postgresql - Aggregating rows into JSON hash -

i have following table:

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

where name can vary value.

i'd json hash so:

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

using pl/pgsql

i'm gonna assume name's can vary cause without it's no challenge. usins eval function:

create or replace function eval(expression text) returns text $body$ declare   result text; begin   execute expression result;   return result; end; $body$ language plpgsql 

we can create dynamic crosstab:

select eval('select row_to_json(q) (select '||(select string_agg(distinct '"'||name||'"',',')                  roles)||'       crosstab(''select 1,name,array_agg(person)::text[]                       roles                       group name;                     '') ct(row_name int,'||(select string_agg(distinct '"'||name||'"                   text[]',',') roles)||') )as q'); 

edit

less fancy aproach:

select '{'||string_agg(temp,',')||'}' ( select '"'||name||'": ['||(string_agg(person,',')::text)||']' temp roles group name ) q