Postgresql group by query to convert json format

Question

My tabla data as follows;

_id	product_id	product_name	sell_price	purchase_price	country
1	prd_1	product one	15	12	usa
2	prd_1	product one	16	13	canada
3	prd_2	product two	7	5	china
4	prd_2	product two	12	10	japan

I want to group it on product basis and convert it to json format. I want the final version as follows;

{ 
    "_id" : "prd_1", 
    "details" : [
        {
            "_id" : 1, 
            "product_id" : "prd_1", 
            "product_name" : "product one", 
            "sell_price" : 15, 
            "purchase_price" : 12, 
            "country" : "usa"
        }, 
         {
            "_id" : 2, 
            "product_id" : "prd_1", 
            "product_name" : "product one", 
            "sell_price" : 16, 
            "purchase_price" : 13, 
            "country" : "canada"
        },   
    ]
}
{ 
    "_id" : "prd_2", 
    "details" : [
        {
            "_id" : 3, 
            "product_id" : "prd_2", 
            "product_name" : "product two", 
            "sell_price" : 7, 
            "purchase_price" : 5, 
            "country" : "china"
        }, 
         {
            "_id" : 4, 
            "product_id" : "prd_2", 
            "product_name" : "product two", 
            "sell_price" : 10, 
            "purchase_price" : 12, 
            "country" : "japan"
        },   
    ]
}

I created the following query but it does not give the result I want. Because I want to only one column all json object. How can we create query that i want? It is possible?

select product_id,
       json_agg(obj) as details
  from (
    select product_id, json_build_object(
           '_id', _id,
           'product_id', product_id,
           'product_name', product_name,
           'sell_price', sell_price,
           'purchase_price', purchase_price,
           'country', country
         ) as obj
  from products p) tmp
group by product_id;

Answer 1

You're close. Just need to wrap the entire thing in another json_agg() to combine it into one row:

edb=# select jsonb_pretty(json_agg(tb)::jsonb) from (
  select _id, json_agg(obj) as details
  from (
    select product_id as _id, json_build_object(
           '_id', _id,
           'product_id', product_id,
           'product_name', product_name,
           'sell_price', sell_price,
           'purchase_price', purchase_price,
           'country', country
         ) as obj
    from products p) tmp
    group by _id) as tb;
                  jsonb_pretty                  
------------------------------------------------
 [                                             +
     {                                         +
         "_id": "prd_2",                       +
         "details": [                          +
             {                                 +
                 "_id": 3,                     +
                 "country": "china",           +
                 "product_id": "prd_2",        +
                 "sell_price": 7,              +
                 "product_name": "product two",+
                 "purchase_price": 5           +
             },                                +
             {                                 +
                 "_id": 4,                     +
                 "country": "japan",           +
                 "product_id": "prd_2",        +
                 "sell_price": 12,             +
                 "product_name": "product two",+
                 "purchase_price": 10          +
             }                                 +
         ]                                     +
     },                                        +
     {                                         +
         "_id": "prd_1",                       +
         "details": [                          +
             {                                 +
                 "_id": 1,                     +
                 "country": "usa",             +
                 "product_id": "prd_1",        +
                 "sell_price": 15,             +
                 "product_name": "product one",+
                 "purchase_price": 12          +
             },                                +
             {                                 +
                 "_id": 2,                     +
                 "country": "canada",          +
                 "product_id": "prd_1",        +
                 "sell_price": 16,             +
                 "product_name": "product one",+
                 "purchase_price": 13          +
             }                                 +
         ]                                     +
     }                                         +
 ]
(1 row)