创建一个嵌套json，列值作为键值对

Question

我正在尝试从以下表构建JSON

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       |
+------+---------+-----------+

我正在尝试创建这种结构的嵌套JSON:

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

---

但我能做的最好的是这个查询

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

是-

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

---

所以，我怎么能纠正我当前的查询替换嵌套json数组与json元素的键值对从列值?

这里是我尝试过的样本数据和查询的提琴

Answer 1

您可以使用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

看到小提琴。