Say we have 2 collections in a Mongo Atlas database.

• Users
• Inventory

Users has name and userId

Inventory has inventoryId, userId

I want to search for all the inventory items by name of the user. There can be multiple entries in the user table with the same name. What is an efficient way to do this, which can also handle a lot of documents in these 2 collections?

CodePudding user response：

From what I know the most efficient way is to use $lookup, but it is only available in a stage of an aggregation pipeline

mongo.collection('users').aggregate([
    ...
    {$lookup: {
        from: "inventory",        //name of the collection           
        localField: "userId",     //localField
        foreignField: "userId",   //foreignField
        as: "inventory".          //as <=> name in the document
    }},
    ...
]).toArray(),

This kind of doing usually require data manipulation because the new field added is an array.

CodePudding user response：

In general, if you want to search a lot of documents, in two collections, where text is one of the filter criteria (in this case name), the best solution is often lookup.

Here is an example modified from the sample_mflix dataset on the steps you would need to cover in your aggregation pipeline:

 var db = Products,
      joinSearchQuery = db.Users.aggregate([
        {
          '$search': {
            'text': {
              'query': 'Ned Stark', // could also be autocomplete to be a nicer feature
              'path': 'name'
            }
          }
        }, {
          '$lookup': {
            'from': 'Inventory', 
            'localField': 'userId', 
            'foreignField': 'userId', 
            'as': 'inventoryFromUser', 
            'pipeline': [
              ...
            ]
          }
        }
      ]);

What's interesting about this query is that the name search aspect could be really expensive and crappy if just a regular text query, as searches are best done using Atlas Search. If there is an interactive search form, autocomplete using $search could also be interesting. There's a free forever tier so it doesn't cost money unless it's very big.