I have two collection one users and another one employees, I want to search with single keyword in first_name,last_name,email,phone_no from the first collection and employee_id,user_type_name from second collection.
I tried with the below query but it doesn’t work
Connection.db.collection('users').aggregate([
{
"$match": {
$and: [{
$or: [
{ first_name: new RegExp(searchString, 'i') },
{ last_name: new RegExp(searchString, 'i') },
{ email: new RegExp(searchString, 'i') },
{ phone_no: new RegExp(searchString, 'i') }
]
},
{
'company_id': ObjectId(req.body.company_id),
is_deleted: 0,
user_type: process.env.EMPLOYEE_USER_TYPE
}]
}
},
{
"$lookup": {
"from": "employees",
"let": {
eId: "$_id"
},
"pipeline": [
{
$match: {
$and: [
{
$or: [
{ employee_id: new RegExp(searchString, 'i') },
{ user_type_name: new RegExp(searchString, 'i') }
]
},
{
$expr: {
$eq: [
"$user_id",
"$eId"
]
}
}
]
}
}
],
"as": "other_details"
}
},
{ $skip: offset },
{ $limit: perPage },
]).toArray((err, result) => {
if (err) throw err;
response = { status: status, msg: "Company user list.", data: result, total_page: total_page_number, total_record: total_record_count };
res.json(response);
});