Learning by Doing

This is just a quick note to get the execution stats for a long query. var exp = db.getSiblingDB("device").upDown.explain("executionStats") exp.aggregate([ { $match: { lastModifiedTimestamp: { $lte: new Date(1569297599999) }, deviceId: { $in: [ { identifier: "001924" }, { identifier: "00D645" }, { identifier: "00D646" }, { identifier: "943000017688" }, { identifier: "005605" }, { identifier: "002217" }, { identifier: "00D641" }, { identifier: "004639" }, { identifier: "943000027922" }, { identifier: "004088" }, { identifier: "990003358538110" }, { identifier: "352648065432767" }, { identifier: "358683065700255" }, { identifier: "004888" }, { identifier: "004646" }, { identifier: "943000018098" }, { identifier: "359739074633462" }, { identifier: "352648065428153" }, { identifier: "...

This issue arises when end user mentioned they couldn't locate certain records and they know the record is indeed in the database and they were using _id to reference the record. Quick investigation of our database, we can see our _id type is split into total count: db.getCollection('upDown').find({}).count() //2403170 count by type 2 which is string db.getCollection('upDown').find({_id:{$type:2}}).count() //1630934 count by not type2 db.getCollection('upDown').find({_id:{$not:{$type:2}}}).count() //772236 check a few sample record, we see the _id is in the default format of bson object, objectId db.getCollection('upDown').find({_id:{$not:{$type:2}}}) This issue was first discovered by end user who uses MongoDB  BI connector to query mongodb data. In the drdl I see we have exposed the field as     - Name: _id       MongoType: string       SqlName: _id       SqlType: varchar from MySQL query, it looks lik...