MongoDB aggregate: return collection if object id same between two collection

Question

This is my code

async isCaseBelongToRiderCompany(userId, caseId): Promise<boolean> {
    const employeeIdFromCase = await this.connection.db
    .collection('cases')
    .findOne({
        _id: new mongo.ObjectID(caseId)
    })
    const employeeIdFromUser = await this.connection.db
    .collection('users-permissions_user')
    .findOne({
        _id: new mongo.ObjectID(userId)
    })
    if(employeeIdFromCase.provider){
        if(employeeIdFromUser.employer){
            if(employeeIdFromCase.provider.toString() === employeeIdFromUser.employer.toString()){
                return true;
            }
        }else{
            return false
        }
    }else{
        return false
    }
}

I want to change this to mongo aggregate so that it will reduce to a single call to MongoDB instead of needing to await two times.

Is there any way I get convert this logic to aggregate?

check [$lookup](https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup/) — 1sina1, Apr 18 '22 at 06:26
can you give some example? i tried using $lookup but it return a list of case that do not match my need — Zulfiqar Laili, Apr 18 '22 at 08:09
Does this answer your question? [How do I perform the SQL Join equivalent in MongoDB?](https://stackoverflow.com/questions/2350495/how-do-i-perform-the-sql-join-equivalent-in-mongodb) — Boaz, Apr 18 '22 at 13:38
Either use `$lookup` to perform a "join" over the two collections (see suggested duplicate) or use `Promise.all()` to run both queries together (there must be several duplicates for that as well). — Boaz, Apr 18 '22 at 13:41

Tom Slabbaert · Accepted Answer · 2022-04-18T13:46:21.823

You can use the following pipeline using $lookup:

async isCaseBelongToRiderCompany(userId, caseId): Promise<boolean> {

    const employeeIdFromUser = await this.connection.db
        .collection('users-permissions_user')
        .aggregate([
            {
                $match: {
                    _id: new mongo.ObjectID(userId),
                },
            },
            {
                $lookup: {
                    from: 'cases',
                    let: { employer: '$employer', caseId: caseId },
                    pipeline: [
                        {
                            $match: {
                                $expr: {
                                    $and: [
                                        {
                                            $eq: [
                                                '$_id',
                                                '$$caseId',
                                            ],
                                        },
                                        {
                                            $eq: [
                                                '$provider',
                                                '$$employer',
                                            ],
                                        },
                                    ],
                                },
                            },
                        },
                    ],
                    as: 'cases',
                },
            },
            {
                $match: {
                    'cases.0': { $exists: true },
                },
            },
        ]).toArray();
    return employeeIdFromUser.length > 0;
}

I personally do not recommend it, the aggregation pipeline has some overhead, combined with the fact that this $lookup syntax does not utilize indexes if you're on Mongo version lesser than 5 makes this less efficient by a mile.

The two separate calls is in fact the best approach as they are both indexed and very lean, you can just make the code a little cleaner:

async isCaseBelongToRiderCompany(userId, caseId): Promise<boolean> {
    const employeeIdFromCase = await this.connection.db
        .collection('cases')
        .findOne({
            _id: new mongo.ObjectID(caseId)
        })

    const employeeIdFromUser = await this.connection.db
        .collection('users-permissions_user')
        .findOne({
            _id: new mongo.ObjectID(userId)
        })
    return employeeIdFromCase?.provider?.toString() === employeeIdFromUser?.employer?.toString()
}

Additionally you can execute both promises at the same time using Promise.all, like so:

async isCaseBelongToRiderCompany(userId, caseId): Promise<boolean> {
    const [employeeIdFromCase, employeeIdFromUser] = await Promise.all([
        this.connection.db
            .collection('cases')
            .findOne({
                _id: new mongo.ObjectID(caseId)
            }),
        this.connection.db
            .collection('users-permissions_user')
            .findOne({
                _id: new mongo.ObjectID(userId)
            })
    ])
    return employeeIdFromCase?.provider?.toString() === employeeIdFromUser?.employer?.toString()
}

Sir this is a perfect answer to my problem. I'll go with the `Promise.all` because aggregate is overkill — Zulfiqar Laili, Apr 27 '22 at 05:50

MongoDB aggregate: return collection if object id same between two collection

1 Answers1