MongoDB按first_name+last_name全名搜索用户失效问题求助

问题原因 #

当前查询逻辑是分别对first_name、last_name等单个字段做正则匹配，当输入全名「John Smith」时，没有任何单个字段包含完整的「John Smith」字符串，因此无法返回匹配结果。

解决方案 #

方案1：拆分搜索词实现多词匹配

将搜索词按空格拆分为多个关键词，要求每个关键词都能匹配至少一个目标字段，既支持单个词搜索，也支持全名搜索。

修改后的代码：

exports.getUsersWithSearchParamAndLimit = async (req, res) => {
    const { searchterm, role } = req.body;
    const page = parseInt(req.params.page);
    const limit = parseInt(req.params.limit);
    const skip = (page - 1) * limit;

    // 拆分搜索词为非空关键词数组
    const keywords = searchterm.trim().split(/\s+/).filter(word => word);
    
    // 构建多词匹配条件：每个关键词需匹配至少一个字段
    const keywordConditions = keywords.map(keyword => ({
        $or: [
            { first_name: { $regex: keyword, $options: "i" } },
            { last_name: { $regex: keyword, $options: "i" } },
            { billing_phone: { $regex: keyword, $options: "i" } },
            { user_email: { $regex: keyword, $options: "i" } },
        ]
    }));

    const query = {
        $and: [
            ...keywordConditions,
            { role: role.length > 0 ? role : { $in: ["customer", "admin"] } },
        ],
    };

    const users = await User.find(query)
        .select(
            "first_name last_name user_email user_verified billing_phone userVerified createdAt disable"
        )
        .limit(limit)
        .skip(skip)
        .sort({ createdAt: "desc" });

    const count = await User.countDocuments(query).exec();

    return res.json({
        status: "SUCCESS",
        users: users,
        count: count,
    });
};

方案2：使用MongoDB文本索引（推荐）

创建包含目标字段的文本索引，利用MongoDB原生全文搜索能力，性能远高于正则匹配，天然支持多词搜索。

步骤1：创建文本索引

在User模型中添加索引：

// User Schema定义中添加
UserSchema.index({ first_name: "text", last_name: "text", user_email: "text", billing_phone: "text" });

或直接在MongoDB Shell执行：

db.users.createIndex({ first_name: "text", last_name: "text", user_email: "text", billing_phone: "text" })

步骤2：修改查询代码

exports.getUsersWithSearchParamAndLimit = async (req, res) => {
    const { searchterm, role } = req.body;
    const page = parseInt(req.params.page);
    const limit = parseInt(req.params.limit);
    const skip = (page - 1) * limit;

    const query = {
        $and: [
            { $text: { $search: searchterm } },
            { role: role.length > 0 ? role : { $in: ["customer", "admin"] } },
        ],
    };

    const users = await User.find(query)
        .select(
            "first_name last_name user_email user_verified billing_phone userVerified createdAt disable"
        )
        .limit(limit)
        .skip(skip)
        .sort({ createdAt: "desc" });

    const count = await User.countDocuments(query).exec();

    return res.json({
        status: "SUCCESS",
        users: users,
        count: count,
    });
};

方案3：拼接字段匹配完整姓名

在查询中通过$concat拼接first_name和last_name，直接匹配完整姓名，保留原有单个字段匹配逻辑。

修改后的代码：

exports.getUsersWithSearchParamAndLimit = async (req, res) => {
    const { searchterm, role } = req.body;
    const page = parseInt(req.params.page);
    const limit = parseInt(req.params.limit);
    const skip = (page - 1) * limit;

    const query = {
        $and: [
            {
                $or: [
                    { first_name: { $regex: searchterm, $options: "i" } },
                    { last_name: { $regex: searchterm, $options: "i" } },
                    { billing_phone: { $regex: searchterm, $options: "i" } },
                    { user_email: { $regex: searchterm, $options: "i" } },
                    // 添加完整姓名匹配条件
                    {
                        $expr: {
                            $regexMatch: {
                                input: { $concat: ["$first_name", " ", "$last_name"] },
                                regex: searchterm,
                                options: "i"
                            }
                        }
                    }
                ],
            },
            { role: role.length > 0 ? role : { $in: ["customer", "admin"] } },
        ],
    };

    const users = await User.find(query)
        .select(
            "first_name last_name user_email user_verified billing_phone userVerified createdAt disable"
        )
        .limit(limit)
        .skip(skip)
        .sort({ createdAt: "desc" });

    const count = await User.countDocuments(query).exec();

    return res.json({
        status: "SUCCESS",
        users: users,
        count: count,
    });
};

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