patternsqlMinor
Multiple rows into json
Viewed 0 times
jsonrowsintomultiple
Problem
Looks my question is unusual because I didn't find the answer at all.
Let's imagine, I have table
language | uri
---------|-----------------
ru | some-uri
en | some-another-uri
...
My question is: How can I return JSON object instead of many rows.
E.g.:
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",
...
}Solution
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)Code Snippets
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)Context
StackExchange Database Administrators Q#205973, answer score: 5
Revisions (0)
No revisions yet.