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 like everything is fine, we can see every record

but when we query using the condition, _id= '5d826963fcc6700001979b13';

we found nothing, returned null.

After turning on profiling level on mongodb to capture every operation, we probably should have already thought about it by just looking at how the data is exposed, but we didn't realize this until I saw how the query is formed. Basically mongosqld translates the query using string as it was instructed to do, therefore, the query executed in mongodb is in the form of

db.getSiblingDB("equipment").upDown.aggregate([ { $match: { _id: '5d826963fcc6700001979b13' } }])

but _id is stored as bson object.

To fix this on drdl, simply modify the way the data is exposed

    - Name: _id
      MongoType: bson.ObjectId
      SqlName: _id
      SqlType: varchar

Now we can find the record as expected via MongoDB BI Connector!

However, the only issue is if you want to reference your data saved in string, we have to use a different column name and reference the same field as such

    - Name: _id
      MongoType: string
      SqlName: _id_string
      SqlType: varchar


so to search for record stored in bson
_id= '5d826963fcc6700001979b13';

to search for record stored in string
_id_str= '5d826963fcc6700001979b13';


cheers!