Find a partial object id in mongosh

Does anyone have a very quick answer for how to query the database for a partial object _id. This should be trivial and in the documentation but so far an hour of Google and documentation has produced no answer.

Problem:

I have a database with about 80,000 records.

With an object id like “61429598a466a229c00d2f03” I want to search the database for a partial record. Specifically, I’d like to search the database for the last 6 characters. I am not looking for duplicates just the one record that matches.

I get that the Object _id is a special kind of data and there is a reference to adding a .str to something in nodeJS. I just want to do a .findOne( { _id : { $regex : ‘0d2f03’ }} and get a response.

Hi @Colin_Bitterfield welcome to the community!

You can do this with an aggregation pipeline. For example:

> db.test.find()
[
  { _id: ObjectId("619ee6a4ce67baae374f846c") },
  { _id: ObjectId("619ee6b5ce67baae374f846d") },
  { _id: ObjectId("619ee6b6ce67baae374f846e") }
]

> db.test.aggregate([
...     {$addFields: {id: {$toString:'$_id'}}},
...     {$match: {id: {$regex:/846c$/}}}
... ])
[
  {
    _id: ObjectId("619ee6a4ce67baae374f846c"),
    id: '619ee6a4ce67baae374f846c'
  }
]

However I would recommend against this approach for a couple of reasons:

  1. The query is very very inefficient. It requires adding a new field for every document in the collection, which is worse than a collection scan.
  2. It is not possible for this query to use any index whatsoever, and the way it works prevents it from being optimized.

I think instead of using this approach, we should rethink why you need to do this in the first place:

  1. Is your _id field not representative of how you’re using it? How about using a custom _id field that’s more aligned to your use case instead of using the default ObjectId?
  2. Searching for the last 6 chars of the ObjectId severely decreases its cardinality. You will get collisions sooner rather than later.

Is there anything about your use case that you can share?

Best regards
Kevin

2 Likes