如何修改PostgreSQL查询将嵌套JSON数组转为键值对JSON对象

现有两张关联表car_makers和cars，表结构及数据如下：

car_makers表 #

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

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

当前使用的PostgreSQL查询：

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

得到的结果中cars字段是JSON数组，而非目标的键值对JSON对象：

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

请问如何修改该查询，将嵌套的JSON数组替换为以列值为键值对的JSON元素？

将原查询中用于生成cars字段的JSON_AGG(json_build_object(...))替换为json_object_agg(c.carname, c.cartype)即可。json_object_agg是PostgreSQL专门用于将两列数据分别作为键和值，聚合生成JSON对象的函数，刚好匹配需求中的键值对结构。

修改后的完整查询：

select json_build_object(
    'companyName', cm.companyname, 
    'carType', cm.country, 
    'cars', json_object_agg(c.carname, c.cartype)
)
from car_makers cm 
join cars c on c.cmid = cm.cmid
group by cm.companyname, cm.country;

执行该查询后，cars字段会生成目标格式的键值对JSON对象，以大众为例的结果如下：

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

内容的提问来源于stack exchange，提问作者an33sh