How to search with same value in two collection?

[Sayan_Sen]

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

[Aasawari (Aasawari Sahasrabuddhe)]

Hi @Sayan_Sen and welcome to MongoDB community forums!!

@Sayan_Sen

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.

From the above posts, the quoted part seems to be unclear to me, it would be very helpful for us to triage and provide you with the assistance if you could provide the following informations:

1. Sample data from both the collections.
2. The expected response you need from the the aggregation query.
3. Indexes defined on both the collections
4. Finally, more information regarding “I tried with the below query but it doesn’t work”. E.g. incorrectly formatted documents returned, incorrect documents returned, error(s), no documents returned, etc.

Thanks
Aasawari