PostgreSQL: row_to_json() inner join problem

user_15802036 0
Viewed 74

I have two tables: product category and products

product category
+----+-------------+
| id | catagory    |
+----+-------------+
| 0  | electronics |
+----+-------------+
| 1  | toys        |
+----+-------------+

products
+----+-----------+-------+--------+
| id | name      | price | ref_id |
+----+-----------+-------+--------+
| 0  | headphone | 100   | 0      |
+----+-----------+-------+--------+
| 1  | phone     | 200   | 0      |
+----+-----------+-------+--------+

How to use row_to_json() to generate the following JSON string:

{
    "catagory": "electronics",
    "products": [
        {
            "name":"headphone",
            "price":100
        },
        {
            "name":"phone",
            "price":200
        }
    ]
}
postgresql
1 Answers

You cannot to build this nested expected nested json document by row_to_json function. But it is not too hard:

select json_build_object('category', c.category, 
                         'products', json_agg(json_build_object('name', name, 
                                                                'price', price))) 
 from products 
      join product_category c 
      on ref_id = c.id 
group by c.category;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                   json_build_object                                                   │
╞═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ {"category" : "electronics", "products" : [{"name" : "headphone", "price" : 100}, {"name" : "phone", "price" : 200}]} │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(1 row)
user_4066910
Related