TypeORM（PostgreSQL）UUID数组字段查询与结果过滤问题

我在TypeORM（PostgreSQL）中实现了基于可选过滤器的搜索功能，原查询代码如下：

queryBuilder.where({
    ...(filter.givenBy && { fromUser: sender.id }),
    ...(filter.receivedBy && { toUser: receiver.id }),
    ...(filter.endDate && { createdAt: LessThanOrEqual(filter.endDate) }),
    ...(filter.startDate && { createdAt: MoreThanOrEqual(filter.startDate) }),
    ...(filter.startDate && filter.endDate && { createdAt: Between(filter.startDate, filter.endDate) })
})

此前toUser为UUID类型，查询正常。现在已通过迁移将toUser改为UUID数组类型（UUID[]）：

之前表结构：

|              to_user                   |
          -----------------------------------------
          |   652afb8a-6bf3-11ed-a1eb-0242ac120002 |

当前表结构：

|                                    to_user                                     |
           ---------------------------------------------------------------------------------
          |   {652afb8a-6bf3-11ed-a1eb-0242ac120002, 99e53e12-6bf3-11ed-a1eb-0242ac120002 }|

我修改查询代码如下：

queryBuilder.where({
    ...(filter.givenBy && { fromUser: sender.id }),
    ...(filter.endDate && { createdAt: LessThanOrEqual(filter.endDate) }),
    ...(filter.startDate && { createdAt: MoreThanOrEqual(filter.startDate) }),
    ...(filter.startDate && filter.endDate && { createdAt: Between(filter.startDate, filter.endDate) })
})

if(filter.receivedBy){
    queryBuilder.andWhere('t.to_user && :data', { data:[receiver.id]})
}

该查询能匹配到数据，但返回的toUser字段包含数组中所有元素，例如当筛选toUser = 652afb8a-6bf3-11ed-a1eb-0242ac120002时，返回结果：

{
    "createdAt": "2022-11-24T10:33:00.914Z",
    "updatedAt": "2022-11-24T10:33:00.914Z",
    "id": "657481cc-0aed-4849-9f86-a2a7ece30a18",
    "fromUser": "12b0b72c-6bf4-11ed-a1eb-0242ac120002",
    "toUser": [
        "652afb8a-6bf3-11ed-a1eb-0242ac120002",
        "99e53e12-6bf3-11ed-a1eb-0242ac120002"
    ],
    "quantity": 9,
    "message": "test110"
}

我期望返回的toUser只包含匹配的UUID，即：

{
    "createdAt": "2022-11-24T10:33:00.914Z",
    "updatedAt": "2022-11-24T10:33:00.914Z",
    "id": "657481cc-0aed-4849-9f86-a2a7ece30a18",
    "fromUser": "12b0b72c-6bf4-11ed-a1eb-0242ac120002",
    "toUser": [
        "652afb8a-6bf3-11ed-a1eb-0242ac120002"
    ],
    "quantity": 9,
    "message": "test110"
}

请问如何修改TypeORM的查询构建器以实现该需求？

有两种可行的方式实现需求，可根据数据量和场景选择：

1. 数据库层面过滤数组元素 #

利用PostgreSQL的数组函数，在查询时直接返回过滤后的toUser数组，减少传输的数据量：

queryBuilder.where({
    ...(filter.givenBy && { fromUser: sender.id }),
    ...(filter.endDate && { createdAt: LessThanOrEqual(filter.endDate) }),
    ...(filter.startDate && { createdAt: MoreThanOrEqual(filter.startDate) }),
    ...(filter.startDate && filter.endDate && { createdAt: Between(filter.startDate, filter.endDate) })
})

if(filter.receivedBy){
    const receiverId = receiver.id;
    // 保留包含目标UUID的记录
    queryBuilder.andWhere('t.to_user && :data', { data: [receiverId] })
    // 替换toUser字段为仅包含匹配UUID的数组
    queryBuilder.addSelect(`array(SELECT unnest(t.to_user) WHERE unnest(t.to_user) = :receiverId)`, 'toUser')
    queryBuilder.setParameter('receiverId', receiverId)
}

注意：如果实体类中toUser字段有特定类型映射，需确保TypeORM能正确解析别名返回的数组；若使用getRawMany()替代getMany()，可能需要手动将结果映射到实体类型。

2. 应用层面过滤 #

查询出完整结果后，在代码中手动过滤每个记录的toUser数组，逻辑更直观：

// 执行原查询获取结果
const results = await queryBuilder.getMany();

if(filter.receivedBy){
    // 过滤每个结果的toUser数组，只保留匹配的UUID
    return results.map(item => ({
        ...item,
        toUser: item.toUser.filter(id => id === receiver.id)
    }));
}

return results;

两种方式对比 #

• 数据库层面过滤：适合数据量较大的场景，减少网络传输的数据量，性能更优。
• 应用层面过滤：代码实现简单，无需修改SQL逻辑，适合数据量较小的场景。

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