HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Multiple rows into json

Submitted by: @import:stackexchange-dba··
0
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 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.