Two queries or aggregate lookup for MongoDB inner join

Question

I have two collections: Manager and Venue.

Manager document example

{
 "_id":"5d4f84dd4c35350b284b08ea",
"Name":"Name",
"Surname":"Surname",
"Username":"Username",
"Password":"123456",
"CreatedDate":"2019-08-11T03:00:44.981Z"
}

Venue document example

{
"_id":"5d4f84de4c35350b284b08eb",
"Name":"Name",
"Manager":"5d4f84dd4c35350b284b08ea",
"CreatedDate":"2019-08-11T03:00:46.598Z"
}

I learned that there are two ways to do inner join

First way

solve this problem in two queries

var manager = db.manager.findOne({"id" : "5d4f84dd4c35350b284b08ea"})

var venue = db.venue.findOne({"id" : manager.id})

Second way

To have everything with just one query using the $lookup feature of the aggregation framework

db.Manager.aggregate(
    [
        {
            $lookup:
            {
                from: "Venue",
                localField: "localField",
                foreignField: "_id",
                as: "as"
            }
        },
    ]
)

Reference Link

here is the question

Second way is more suitable for me but I'm not sure which way to use, can you help me.

Unless needed, you should always avoid multiple calls to DB, which in-turn helps you in multiple ways to avoid (network latency/cost factor/Slowness/ redundant code), if it can be achieved in one go why not to use $lookup if it's been developed to do the same !! Make sure to use proper indexing & have proper querying while using aggregation framework, basically any DB query though.. — whoami - fakeFaceTrueSoul, Sep 07 '19 at 20:03

score 2 · Answer 1 · answered Sep 07 '19 at 19:53

When you use the second way, the benefit is you are performing a single query and letting mongo handle the processing, however, you should limit the documents that go into your lookup clause with possible match options before hand.

In aggregate you can perform a chain of operations, so ideally, in this case, you can perform a lookup for manager id from the first collection using {"$match":{"id" : "5d4f84dd4c35350b284b08ea"}} and in the 2nd case do a join.

In the join too, you may have one to many relations among manager and venue so it will be even better if you can inject some match queries in the lookup to limit the number of documents from the venue collection that you want.

A few points to note are

when you use "$expr" inside lookup clause it does not take advantage of indexes to search.
If there are many fields inside your venue docs and its size is huge, your join query may run out of memory pretty easily. (It is better to use a project clause inside the look up to limit fields to whatever you need)

About the first note you added - "The `$expr` operator only uses indexes on the **from** collection for equality matches. Non-equality match queries, such as range queries, cannot use indexes on the **from** collection" - [mongodb](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#join-conditions-and-uncorrelated-sub-queries) — Alon Arad, May 09 '21 at 08:54

Two queries or aggregate lookup for MongoDB inner join

1 Answers1