Get users by first_name + last_name both in mongodb

Question

The search in my application is not working properly. For eg: If name is "John Smith" and I search by John only (first_name) , I get all the documents by name John and If I type "Smith" only then also it works fine but when I write fullname John(first_name) Smith (last_name) no documents are returned. I want to return documents when I type full name in search box.

The code is below: getUsersWithSearchParamAndLimit => Returns documents

NOTE : searchterm in the below function is what is coming from the search field from my front-end. If I type "John Smith" then it queries in the below function

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

    const users = await User.find({
        $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" } },
                ],
            },
            { role: role.length > 0 ? role : { $in: ["customer", "admin"] } },
        ],
    })
        .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({
        $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" } },
                ],
            },
            { role: role.length > 0 ? role : { $in: ["customer", "admin"] } },
        ],
    }).exec();

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

And my mongodb schema of users is below:

{
    "_id": {
       "$oid": "___________________"
    },
    "id": "11547",
    "first_name": "XXX",
    "last_name": "XX",
    "user_email": "asbc",
    "user_pass": "*****",
    "role": "customer",
    "billing_phone": "1230123",
    "birth_date": "11/07/20",
    "userVerified": true,
    "createdAt": {
       "$date": "2022-05-17T13:50:30.358Z"
    },
    "updatedAt": {
       "$date": "2023-09-11T18:14:39.221Z"
    },
    "__v": 0,
    "disable": false,
    "billingAddress": {
       "city": "Drayton Valley",
       "postalCode": "XXLSSL",
       "province": "Alberta",
       "streetAddress": "26"
    }
}

Answer 1

You can join first_name a space and then last_name together with $concat and then use $regexMatch to look for a hit on that full name combination with regex. It would look like this:

const users = await User.find({
  $and: [
    {
      $or: [
        {
          $expr: {
            $regexMatch: {
              input: {
                $concat: [
                  "$first_name",
                  " ",
                  "$last_name"
                ]
              },
              regex: searchterm,
              options: "i"
            }
          }
        },
        {
          billing_phone: {
            $regex: searchterm,
            $options: "i"
          }
        },
        {
          user_email: {
            $regex: searchterm,
            $options: "i"
          }
        }
      ]
    },
    {
      role: {
        $in: [
          "customer",
          "admin"
        ]
      }
    }
  ]
})

You can then chain your select, limit, skip and sort stages to it.

See HERE for a working example.

Note: billing_phone will need to be a string. You can't do regex on numbers.