I am trying implement Dynamic Searching in MongoDB with Referenced Documents

市场方案:

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

版主方案:

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

现在，我试图处理的文档

主持人到市场映射方案:

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
});

我想要过滤数据的基础上marketName的市场或基于name or username的版主使用中间市场映射文档.

我希望得到的结果类似于以下查询将产生的结果

如果按市场进行搜索

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中搜索‘Markets’，然后从Mapping schema中获取id's of moderator，然后相应地填充数据，类似地，如果我想要按名称或用户名搜索，那么我首先获取moderators，然后从Mapping schema中获取id's of markets，然后填充数据

现在很明显，这是一个很好的方法，当要搜索的数据增长时，它肯定会失败.

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 },
        },
    ]);

如何使about代码工作，或者除了切换到RDBMS之外，还有其他更好的方法吗？