Create JSON value in Postgres SQL from GROUP BY statement

Question

I'm looking to create single JSON object per grouped field, and trying to use json_build_object for that purpose. However, the return result is multiple json values instead.

Below is the code

with t(product, key, value) as 
(
    values 
    ('A','category', 'food'),
    ('A','price', '4.00'),
    ('B','category', 'book'),
    ('B','price', '20.00'),
    ('B','genre', 'Sci-Fi')
)
select product, json_build_object(key, value) from t
group by product, key, value

and the result received is

"A" | "{""price"" : ""4.00""}"
"A" | "{""category"" : ""food""}"
"B" | "{""genre"" : ""Sci-Fi""}"
"B" | "{""category"" : ""book""}"
"B" | "{""price"" : ""20.00""}"

What I want as result is

"A" | "{ 'price' : 4.00, 'category': 'food' }"
"B" | "{ 'genre' : 'Sci-Fi', 'category': 'book', 'price': 20.00 }"

score 2 · Accepted Answer · 2020-10-20 11:10:37Z

2

That's what jsonb_object_agg() is for:

select product, jsonb_object_agg(key, value) 
from t
group by product

Online example

edited Oct 20, 2020 at 11:10

answered Oct 20, 2020 at 11:04

user330315

Sign up to request clarification or add additional context in comments.

Comments

Laurenz Albe · Accepted Answer · 2020-10-20 11:13:44Z

2

You can also do it with json_object and array_agg:

with t(product, key, value) as 
(
    values 
    ('A','category', 'food'),
    ('A','price', '4.00'),
    ('B','category', 'book'),
    ('B','price', '20.00'),
    ('B','genre', 'Sci-Fi')
)
select product, json_object(array_agg(key), array_agg(value)) from t
group by product;

 product |                       json_object                        
---------+-----------------------------------------------------------
 A       | {"price": "4.00", "category": "food"}
 B       | {"genre": "Sci-Fi", "price": "20.00", "category": "book"}
(2 rows)

answered Oct 20, 2020 at 11:13

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Collectives™ on Stack Overflow

Create JSON value in Postgres SQL from GROUP BY statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related