Create a nested json with column values as key-value pairs

December 16, 2022

I am trying to build a JSON from the following tables

table : car_makers
+------+-------------+---------+
| cmid | companyname | country |
+------+-------------+---------+
|    1 | Toyota      | Japan   |
|    2 | Volkswagen  | Germany |
|    3 | Nissan      | Japan   |
+------+-------------+---------+

Table : cars 

+------+---------+-----------+
| cmid | carname |  cartype  |
+------+---------+-----------+
|    1 | Camry   | Sedan     |
|    1 | Corolla | Sedan     |
|    2 | Golf    | Hatchback |
|    2 | Tiguan  | SUV       |
|    3 | Qashqai | SUV       |
+------+---------+-----------+

I am trying to create a nested JSON of this structure :

{
  "companyName": "Volkswagen",
  "carType": "Germany",
  "cars": {
    "Tiguan": "SUV",
    "Golf": "Hatchback"
  }
}

but the best I could do with the this query

select json_build_object('companyName',companyName, 'carType', country, 'cars', JSON_AGG(json_build_object('carName', carName, 'carType', carType) ))
from car_makers cm 
join cars c on c.cmid = cm.cmid
group by companyName,country

is this –

{
  "companyName": "Volkswagen",
  "carType": "Germany",
  "cars": [
    {
      "carName": "Tiguan",
      "carType": "SUV"
    },
    {
      "carName": "Golf",
      "carType": "Hatchback"
    }
  ]
}

So, how can I correct my current query to replace the nested json array with a json element of key-value pairs from column values ?

here is the fiddle with sample data and the query I have tried

>Solution :

You can use json_object_agg:

select json_build_object('companyName', c.companyName, 
   'country', c.country, 'cars', json_object_agg(c1.carName, c1.carType)) 
from car_makers c join cars c1 on c.cmid = c1.cmid
group by c.companyName, c.country

See fiddle.