7

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", ... }

2
  • 1
    Please tag your DBMS (version included)
    – McNets
    May 6 '18 at 13:25
  • @McNets it is about PostgreSQL May 6 '18 at 20:35
2

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)
1
  • 1
    You are right, thanks. Since it was posted a couple of years ago, I cannot implement your solution there 😀 but it definitely might be helpful to others. May 13 at 10:18
9

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.

1
  • 2
    You will need to cast the fields (like language and uri) to ::text in case they are not text
    – Ranjeet
    Oct 8 '19 at 22:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.