时间戳

首页 > 解决方案 > typeorm/postresql - 创建一个按列分组的查询，聚合另一列并获取聚合数组的关系

typescript - typeorm/postresql - 创建一个按列分组的查询，聚合另一列并获取聚合数组的关系

问题描述

我在数据库中有两个具有一对多关系的表。架构如下所示：

types
-------------------------------
| id | brand      | type      |
-------------------------------
| 1  | Ford       | City car  |
-------------------------------
| 2  | Volkswagen | SUV       |
-------------------------------
| 3  | Ford       | SUV       |
-------------------------------
| 4  | Volkswagen | City car  |
-------------------------------

models
--------------------------
| id | type_id | model   |
--------------------------
| 1  | 4       | UP!     |
--------------------------
| 2  | 1       | Fiesta  |
--------------------------
| 3  | 2       | Tiguan  |
--------------------------
| 4  | 2       | Touareg |
--------------------------
| 5  | 3       | Kuga    |
--------------------------

我想要获得的是一个结构如下的 json：

[{
   "type": "City car",
   "brands": [{
      "Ford": [
         { "model": "Fiesta" }
      ],
      "Volkswagen": [
         { "model": "UP!" }
      ],
   }]
},
{
   "type": "SUV",
   "brands": [{
      "Ford": [
         { "model": "Kuga" }
      ],
      "Volkswagen": [
         { "model": "Touareg" },
         { "model": "Tiguan"  },
      ],
   }]
}]

实际上，我要做的是按type列对记录进行分组，将同一类型的所有不同品牌聚合到一个数组中，然后为每种类型+品牌组合获取不同的模型。

我正在postgresql使用typeorm.

标签： typescriptpostgresqlgroup-byaggregate-functionstypeorm

解决方案

试试这个对 Postgresql 的查询（我在 Postgresql 版本 13 中测试过）：

SELECT  
    json_agg(types::jsonb || brands::jsonb)
   
FROM (

    SELECT 
        json_build_object('type', "type") AS types,
        json_build_object('brands', json_object_agg(key, value)) AS brands

    FROM (

        SELECT 
            "type",
            json_build_object(brand, json_object_agg(key, value)) AS brands

        FROM 

            (SELECT 
                t."type", 
                t.brand, json_build_object('model', json_agg(m.model)) AS models
            FROM types t 
            INNER JOIN models m ON t.id = m.type_id
            GROUP BY t."type", t.brand) AS a, json_each(models)

        GROUP BY "type", brand) b, json_each(brands)

    GROUP BY "type") c

结果：

[{"type": "SUV", "brands": {"Ford": {"model": ["Kuga"]}, "Volkswagen": {"model": ["Tiguan", "Touareg"]}}}, {"type": "City car", "brands": {"Ford": {"model": ["Fiesta"]}, "Volkswagen": {"model": ["UP!"]}}}]

推荐阅读