CODEWITHSUNDEEP
jsonpostgresql
idazuwaika
idazuwaika

Reputation: 3019

Create JSON value in Postgres SQL from GROUP BY statement

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

Upvotes: 2

Views: 807

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246238

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)

Upvotes: 2

user330315
user330315

Reputation:

That's what jsonb_object_agg() is for:

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

Online example

Upvotes: 2

Related Questions