MongoDB - directly query a unique index instead of a collection

Question

Looking for way to directly query a MongoDB index, instead of the collection that the index belongs to.

For example, here are two collections:

User:

{
id: ObjectId(),
username: string
...
}

SpecialUser:

{
id: ObjectId(),
specialUsername: string
...
}

Now, let's assume that we have a unique index on the SpecialUser.specialUsername field.

We want to find the id(s) of all User documents which have their username appear in the SpecialUser.specialUsername field.

Now, we can easily do this with the following query:

db.User.aggregate([ 
  { $lookup: {from: "SpecialUser", localFieldName: "username", foreignField: "specialUsername", as "spUser"} }, 
  { $unwind: { path: "$spUser", preserveNullAndEmptyArrays: false} }, 
  { $project: {_id: 1}}
])

However, in this particular case, we are using a lookup and therefore fetching the SpecialUser collection although we don't need any information from that particular collection.

Is it possible to directly use the unique index instead of the $lookup operation for this particular case?

Answer 1

Is it possible to directly use the unique index instead of the $lookup operation for this particular case?

How do you know this unique index is not used by it? You can try "explain" and check the output.

Given the lookup query is only using the indexed filed, i suppose (need verify of course) that it's a covered query, which means only index tree will be examined, and no disk fetch is done for that stage.

mongodb - $lookup pipeline using COLLSCAN instead of index This post may be related

Answer 2

n MongoDB, you cannot directly query an index without querying the collection it belongs to. Indexes are used internally by MongoDB to optimize query performance by providing a more efficient way to search through the documents in a collection. They are not separate entities that can be queried directly.

However, if you want to optimize your query further and reduce the amount of data fetched from the SpecialUser collection, you can use the $project stage in the aggregation pipeline to limit the fields returned from the SpecialUser collection. In this case, you only need the _id field from the SpecialUser collection.

Here's an example of how to do this:

db.User.aggregate([
  {
    $lookup: {
      from: "SpecialUser",
      localField: "username",
      foreignField: "specialUsername",
      as: "spUser",
    },
  },
  { $unwind: { path: "$spUser", preserveNullAndEmptyArrays: false } },
  {
    $project: {
      _id: 1,
      spUser: { _id: 1 }, // Limit the fields returned from the SpecialUser collection
    },
  },
]);

By projecting only the necessary fields, you can minimize the amount of data transferred between the User and SpecialUser collections, potentially improving the performance of your query. However, it's essential to understand that this optimization still involves a $lookup operation and accesses both collections.