How to find all records in MongoDB whose primary key is a user defined character string 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];
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.