1

I would like to know the command to find all records in MongoDB whose primary key (_id) is a user defined character string, or find all records where all primary keys (_id) are not the 12-byte ObjectID BSON type.

For example, in SQL the query might be similar to:

select id from restaurants where id not like "%ObjectId("574d35bda039438e02236caf")%"

or

select id from restaurants where id in $regex[a-z];
3

You can only use the $regex operator to match values that are strings. As you've noted, an ObjectID is a 12-byte binary value stored as a specific BSON type.

You should instead use the $type operator to query based on the BSON data type of a field.

Example usage of $type:

// Find all restaurants where `_id` is not an ObjectId (type 7)
db.restaurants.find({_id: { $not: { $type: 7}} })

// Find all restaurants where `_id` is a string (type 2)
db.restaurants.find({_id: { $type: 2} })

Note: the BSON type is a numeric value, but MongoDB 3.2+ also has friendlier string aliases (i.e. the string "objectId" is equivalent to the numeric $type value of 7 in MongoDB 3.2).

If you already know the _id value you are searching for is a string, you also have the option of using the $regex operator to match a more specific pattern without specifying $type; the non-string values will be ignored by $regex.

For example:

// Find all restaurants with an `_id` starting with Happy (case-sensitive)
db.restaurants.find({_id: /^Happy/})

// Find all restaurants with an `_id` only using word characters (`\w` is `[A-Za-z0-9_]`)
db.restaurants.find({_id: /^[\w]+$/})

If you are running any of these queries regularly against a large collection (or have concerns on performance impact), you should review index usage via explain results. In particular, negated queries and $regex queries have some caveats on efficient index usage.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.