MongoDB tips : check for types, bi connector issue and resolution
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...