postgres inverse of json_each

Question

I don't have json text in a field. I have a row with columns "text","format","keyname","..." and I want to export it to json in the format '{"value of keyname column": {"text":"value of text","format":"value of format",... }}'. Is that possible in postgresql?

I have been looking at a postgis2geojson example and learned about the row_to_json function already. http://www.postgresonline.com/journal/archives/267-Creating-GeoJSON-Feature-Collections-with-JSON-and-PostGIS-functions.html

Furthermore I have been looking at the postgres json documentation: http://www.postgresql.org/docs/9.3/static/functions-json.html

Unfortunately I found no function that covers my need explicitly but json_each does what I want just in the opposite direction. Generally, I think the json functions in postgres are designed on the assumption that only the column names can be used as keys. Am I right? Is there a SQL hack I could use to come around this? Thanks for any help.

EDIT:

select '"'||keyname||'":"'||row_to_json((select r from(Select text, format,
    (select username from my.users where users.id = table.uid) as username,
    machinename ) as r ))||'"'
    from my.table where id = 1;

Answer 1

Since version 9.4 postgres has json_object_agg function, which is the inverse of json_each. You can read the documentation here.

If the data look like this:

> select * from example;
 text | format | keyname 
------+--------+---------
 foo  | bar    | a
 dead | beef   | b
(2 rows)

Then you can aggregate all of that into an object with keys a and b with the following query:

> select json_object_agg(keyname, json_build_object('text', text, 'format', format)) from example;
                                     json_object_agg                                      
------------------------------------------------------------------------------------------
 { "a" : {"text" : "foo", "format" : "bar"}, "b" : {"text" : "dead", "format" : "beef"} }
(1 row)