Multiple rows into json

Question

Looks my question is unusual because I didn't find the answer at all. Let's imagine, I have table A with columns: language and uri.

language | uri             
---------|-----------------
ru       | some-uri        
en       | some-another-uri
...

My question is: How can I return JSON object instead of many rows. E.g.:

{ "ru": "some-uri", "en": "some-another-uri", ... }

Answer 1

There's another way that doesn't require type casting: json_object_agg(name, value)

test=# create table t (name text, value int);
CREATE TABLE
test=# insert into t values ('key1', 1), ('key2', 2), ('key3', 3);
INSERT 0 3
test=# select * from t;
 name | value
------+-------
 key1 |     1
 key2 |     2
 key3 |     3
(3 rows)

test=# select json_object_agg(name, value) from t;
            json_object_agg
----------------------------------------
 { "key1" : 1, "key2" : 2, "key3" : 3 }
(1 row)

Answer 2

So, I found out the answer far far away in the documentation.

SELECT json_object(array_agg(language), array_agg(uri)) FROM A will give you the expected result.