PostgreSQL数据库设计新手问询：过滤器与车辆数据分类关联及目标格式查询实现

嘿，我来帮你搞定这个PostgreSQL数据库设计的问题，结合你的需求和更新后的表结构，给你清晰的解决方案：

问题背景 #

你作为PostgreSQL新手，目前有以下实体表：

1. user表 #

2. carsData1表 #

category为枚举类型[p1,p2,p3]，约束：每个用户的每个分类下的车辆在carsData1和carsData2中必须唯一。

3. carsData2表 #

4. filters表 #

用户可对carsData1、carsData2和filters执行CRUD操作：

核心需求 #

• 将每个过滤器分配到carsData1表的对应分类
• 支持用户将过滤器应用到carsData1或carsData2的分类
• 知晓每个过滤器已应用到哪些车辆

现有解决方案思路 #

方案一：合并为JSONB表+分配表 #

取消carsData1和carsData2，创建carsData表用jsonb存储车辆数据，同时用assign_filters关联过滤器与分类：

carsData表

assign_filters表

方案优势：

• 每个用户在carsData表最多仅需4条记录
• 过滤器与用户分类的分配更简便
• 数据查询更轻松
• 所需表数量更少

查询语句：

select cd.uid, cd.category, cd.id, f1."name" as filter_name, f1."filter", 
       (select jsonb_agg(t -> 'car_id') from jsonb_array_elements(cd.data_jsonb) as x(t)) as car_id 
from carsData cd 
left join assign_filters af on cd.id = af.carsData_id 
left join filters f1 on f1.id = af.filters_id;

查询结果示例：

方案二：Pivot表思路 #

你提到考虑为carsData1和carsData2分别定义pivot表，但不清楚如何实现查询和过滤器分配，下面针对你更新后的需求来解决这个问题。

更新需求：合并carsData表后的查询方案 #

你现在合并了carsData1和carsData2为一张表（假设名为carsData，结构包含原两表的所有字段，price允许为NULL，因为carsData2原本没有这个字段；category新增CD2枚举值），同时assign_filters表结构改为：

要得到你期望的结果格式，我们可以用以下查询语句：

select
    af.filters_id as id,
    af.category,
    af.uid,
    f.name as filter_name,
    f.filter,
    array_agg(c.car_id) as car_id
from assign_filters af
join filters f on af.filters_id = f.id
join carsData c on af.uid = c.uid and af.category = c.category
group by af.filters_id, af.category, af.uid, f.name, f.filter
order by af.uid, af.category;

语句解释 #

1. 关联表：通过assign_filters关联filters（获取过滤器名称和内容），再关联合并后的carsData（获取该用户对应分类下的所有车辆ID）
2. 聚合车辆ID：用array_agg(c.car_id)把同一过滤器、同一用户分类下的车辆ID聚合为数组，和你期望的结果格式匹配
3. 分组：按过滤器ID、分类、用户ID、过滤器名称和内容分组，确保每组对应一个过滤器-用户-分类的组合

预期查询结果 #

这个查询会输出和你要求完全一致的格式：

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