MongoDB关联文档动态搜索：跨集合字段匹配查询问题

Market Schema： #

const MarketSchema = new mongoose.schema({          
    marketName: String,          
    // ... rest of the fields  
}); 

Moderator Schema： #

const ModeratorSchema = new mongoose.Schema({
    name: String,
    username: String,
    // ... rest of the fields
});

当前我需要处理的文档如下

ModeratorToMarketMapping Schema： #

const ModeratorToMarketMappingSchema = new mongoose.Schema({
    moderatorId: {
        type: mongoose.Schema.Types.ObjectId,
        ref: "User",
    },
    marketId: {
        type: mongoose.Schema.Types.ObjectId,
        ref: "Market",
    },
    // ... rest of the fields
});

我希望通过ModeratorToMarketMapping文档，根据Market的marketName或Moderator的name/username过滤数据，期望得到与以下SQL查询类似的结果：

按市场搜索时

SELECT * 
FROM Users AS u
JOIN ModeratorToMarketMapping AS map ON  u.id = map.moderatorId 
JOIN Market AS m ON map.marketId = m.id
WHERE m.marketName LIKE '%${FILTER_VALUE}%'

按姓名/用户名搜索时

SELECT * 
FROM Users AS u 
JOIN ModeratorToMarketMapping AS map ON  u.id = map.moderatorId 
JOIN Market AS m ON map.marketId = m.id
WHERE u.name LIKE '%${FILTER_VALUE}%' OR u.username LIKE '%${FILTER_VALUE}%'

我想到了两种实现方案：

1] 效率较低但可用的方案 #

若按市场过滤，先从Market Collection查询匹配的市场，再从映射集合获取对应的moderator id，最后填充数据；若按姓名/用户名过滤，则先查询匹配的moderators，再获取对应的market id并填充数据。显然该方案在数据量增大时性能会严重下降。

2] 看似高效但部分失效的方案 #

我查找资料后得到以下代码，但无法实现预期效果：

const { criteriaKey, filterValue } = req.query;

let query = {};

if (criteriaKey && filterValue) {
    const regex = new RegExp(filterValue, "i");

    if (criteriaKey === "market") {
        query = { "market.marketName": { $regex: regex } };
    } else if (criteriaKey === "name") {
        query = {
            $or: [
                { "moderator.name": { $regex: regex } },
                { "moderator.username": { $regex: regex } },
            ],
        };
    }
}

const moderators = await ModeratorMarketMapping.aggregate([
    {
        $match: query,
    },
    {
        $lookup: {
            from: "markets",
            localField: "marketId",
            foreignField: "_id",
            as: "market",
        },
    },
    {
        $lookup: {
            from: "users",
            localField: "moderatorId",
            foreignField: "_id",
            as: "moderator",
        },
    },
    {
        $unwind: { path: "$market", preserveNullAndEmptyArrays: true },
    },
    {
        $unwind: { path: "$moderator", preserveNullAndEmptyArrays: true },
    },
]);

如何修正上述代码，或是有无其他更优方案（不切换到关系型数据库）？

问题分析与修正方案 #

原有代码失效的核心原因是**$match阶段放在了$lookup之前**，此时market和moderator字段还未被关联生成，基于这些字段的过滤条件无法生效。以下是两种可行的修正方案：

方案1：调整聚合阶段顺序（基础修复）

将$match移至所有关联、展开操作之后，确保过滤时能访问到关联后的字段：

const { criteriaKey, filterValue } = req.query;

let matchStage = {};

if (criteriaKey && filterValue) {
    const regex = new RegExp(filterValue, "i");

    if (criteriaKey === "market") {
        matchStage = { "market.marketName": { $regex: regex } };
    } else if (criteriaKey === "name") {
        matchStage = {
            $or: [
                { "moderator.name": { $regex: regex } },
                { "moderator.username": { $regex: regex } },
            ],
        };
    }
}

const moderators = await ModeratorMarketMapping.aggregate([
    // 先关联市场和用户数据
    {
        $lookup: {
            from: "markets",
            localField: "marketId",
            foreignField: "_id",
            as: "market",
        },
    },
    {
        $lookup: {
            from: "users",
            localField: "moderatorId",
            foreignField: "_id",
            as: "moderator",
        },
    },
    // 展开数组字段
    {
        $unwind: { path: "$market", preserveNullAndEmptyArrays: true },
    },
    {
        $unwind: { path: "$moderator", preserveNullAndEmptyArrays: true },
    },
    // 最后执行过滤
    {
        $match: matchStage,
    },
]);

方案2：带条件的$lookup（高效优化）

如果数据量较大，先关联再过滤会处理大量无关数据，推荐在$lookup内部使用pipeline前置过滤，减少后续数据处理量：

按市场名称搜索

if (criteriaKey === "market") {
    const regex = new RegExp(filterValue, "i");
    const moderators = await ModeratorMarketMapping.aggregate([
        {
            $lookup: {
                from: "markets",
                let: { mapMarketId: "$marketId" },
                pipeline: [
                    {
                        $match: {
                            $expr: { $eq: ["$_id", "$$mapMarketId"] },
                            marketName: { $regex: regex }
                        }
                    }
                ],
                as: "market"
            }
        },
        { $unwind: "$market" }, // 仅保留关联到匹配市场的记录
        {
            $lookup: {
                from: "users",
                localField: "moderatorId",
                foreignField: "_id",
                as: "moderator"
            }
        },
        { $unwind: "$moderator" }
    ]);
}

按用户姓名/用户名搜索

if (criteriaKey === "name") {
    const regex = new RegExp(filterValue, "i");
    const moderators = await ModeratorMarketMapping.aggregate([
        {
            $lookup: {
                from: "users",
                let: { mapModId: "$moderatorId" },
                pipeline: [
                    {
                        $match: {
                            $expr: { $eq: ["$_id", "$$mapModId"] },
                            $or: [
                                { name: { $regex: regex } },
                                { username: { $regex: regex } }
                            ]
                        }
                    }
                ],
                as: "moderator"
            }
        },
        { $unwind: "$moderator" }, // 仅保留关联到匹配用户的记录
        {
            $lookup: {
                from: "markets",
                localField: "marketId",
                foreignField: "_id",
                as: "market"
            }
        },
        { $unwind: "$market" }
    ]);
}

性能优化建议 #

1. 创建索引：

   • 为markets.marketName创建文本索引：db.markets.createIndex({ marketName: "text" })（适合全文搜索场景），或单字段索引：db.markets.createIndex({ marketName: 1 })（适合前缀正则匹配）
   • 为users.name和users.username创建复合索引：db.users.createIndex({ name: 1, username: 1 })
   • 为映射集合的关联字段创建索引：db.moderatorToMarketMappings.createIndex({ moderatorId: 1, marketId: 1 })

2. 优化正则查询：优先使用前缀匹配（如^${filterValue}）以命中索引；若必须全模糊匹配，优先使用文本索引替代正则。

3. 分页处理：添加$skip和$limit阶段，避免一次性加载大量数据。

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