MongoDB oplog has records with dots in key names, which can't be queried for, afaict

Question

Given: Mongo allows setting nested fields using "dot"s, e.g.:

rs0:PRIMARY> db.tmp.update({ a: 1 }, { $set: { 'b.c': 2 } }, { upsert: true })
rs0:PRIMARY> db.tmp.findOne()
{
    "_id" : ObjectId("558251c6a3354af70d70f3cc"),
    "a" : 1,
    "b" : {
        "c" : 2
    }
}

In this example, the record was created by the upsert, which I can verify in the oplog:

rs0:PRIMARY> use local
rs0:PRIMARY> db.oplog.rs.find().sort({ts:-1}).limit(1).pretty()
{
    "ts" : Timestamp(1434603974, 2),
    "h" : NumberLong("2071516013149720999"),
    "v" : 2,
    "op" : "i",
    "ns" : "test.tmp",
    "o" : {
        "_id" : ObjectId("558251c6a3354af70d70f3cc"),
        "a" : 1,
        "b" : {
            "c" : 2
        }
    }
}

When I do the same thing and the record is merely updated, not created, I seem to get the same behavior:

rs0:PRIMARY> db.tmp.update({ a: 1 }, { $set: { 'b.d': 3 } }, { upsert: true })
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
rs0:PRIMARY> db.tmp.findOne()
{
    "_id" : ObjectId("558251c6a3354af70d70f3cc"),
    "a" : 1,
    "b" : {
        "c" : 2,
        "d" : 3
    }
}

However, this time the entry in the oplog is structured differently:

rs0:PRIMARY> use local
rs0:PRIMARY> db.oplog.rs.find().sort({ts:-1}).limit(1).pretty()
{
    "ts" : Timestamp(1434604173, 1),
    "h" : NumberLong("-4353495487634403370"),
    "v" : 2,
    "op" : "u",
    "ns" : "test.tmp",
    "o2" : {
        "_id" : ObjectId("558251c6a3354af70d70f3cc")
    },
    "o" : {
        "$set" : {
            "b.d" : 3
        }
    }
}

(Note the "b.d" key).

This is causing me issues because I am trying to investigate some dropped updates by inspecting the corresponding oplog entries, but AFAICT there's no way to query for oplog entries that set specific nested fields:

rs0:PRIMARY> db.oplog.rs.findOne({ 'o.$set.b.d': { $exists: true } })
null

Is there any way to query the oplog for entries that pertain to updates of a specific nested field (in this case b.d)?

It seems that I am running into inconsistent application of Mongo's prohibition of dots in field-names: on one hand I can't create (via official clients / directly in the Mongo shell) or query for them, but on the other it is creating them in the oplog, leaving unqueryable oplog entries.

Any help would be much appreciated.

For completeness, note that I can successfully query for oplog entries with keys that include the $set bit:

rs0:PRIMARY> db.tmp.update({ a: 1 }, { $set: { e: 4 } }, { upsert: true })
rs0:PRIMARY> use local
rs0:PRIMARY> db.oplog.rs.findOne({ 'o.$set.e': { $exists: true } })
{
    "ts" : Timestamp(1434604486, 1),
    "h" : NumberLong("1819316318253662899"),
    "v" : 2,
    "op" : "u",
    "ns" : "test.tmp",
    "o2" : {
        "_id" : ObjectId("558251c6a3354af70d70f3cc")
    },
    "o" : {
        "$set" : {
            "e" : 4
        }
    }
}

Answer 1

While it's true that you cannot query for "b.d" directly via find there are workarounds for the issue (since you are trying to do this for debugging purpose, the workaround would allow you to find all the records that match the format of the update you want).

edit See the bottom of the answer for aggregation workaround.

Use mapReduce to output the ts (Timestamp) value(s) of the oplog records you want to match:

map = function() {
    for (i in this.o.$set) 
       if (i=="b.d") emit(this.ts, 1);
}

reduce = function(k, v) { return v; }

db.oplog.rs.mapReduce(map,reduce,{out:{inline:1},query:{op:"u","o.$set":{$exists:true}}})
{
  "results" : [
    {
        "_id" : Timestamp(1406409018, 1),
        "value" : 1
    },
    {
        "_id" : Timestamp(1406409030, 1),
        "value" : 1
    },
    {
        "_id" : Timestamp(1406409042, 1),
        "value" : 1
    },
    {
        "_id" : Timestamp(1406409053, 1),
        "value" : 1
    }
  ],
  "timeMillis" : 117,
  "counts" : {
    "input" : 9,
    "emit" : 4,
    "reduce" : 0,
    "output" : 4
  },
  "ok" : 1
}

db.oplog.rs.find({ts:{$in:[Timestamp(1406409018, 1), Timestamp(1406409030, 1), Timestamp(1406409042, 1), Timestamp(1406409053, 1)]}})
< your results if any here >

In the map function, replace "b.d" with the dotted field name that you are looking for.

If you want to get fancy, you can map a constant and emit the "$in" document, and then use it in your query (same result, slightly different format):

map2=function () {
   for (i in this.o.$set)
      if (i=="b.d") emit(1, {"$in": [ this.ts ]});
}
reduce2=function (k, v) {
   result={"$in": [ ] };
   v.forEach(function(val) {
      val.$in.forEach(function(ts) {
          result.$in.push(ts);
      });
   });
   return result;
}

I can run this version in the shell and on my test data get something like this:

tojsononeline(db.oplog.rs.mapReduce(map2, reduce2, { out:{inline:1}, query:{op:"u","o.$set":{$exists:true}}}).results[0].value)
{  "$in" : [ Timestamp(1406409042, 1), Timestamp(1406409018, 1), Timestamp(1406409030, 1), Timestamp(1406409053, 1) ] }

EDIT Turns out there is also a way to run the query directly via aggregation framework:

db.oplog.rs.aggregate( [
    {$match:{"o.$set":{$exists:true}}},
    {$project: { doc:"$$ROOT", 
                 matchMe:{$eq:["$o",{$literal:{$set:{"b.d":1 }}}]}
    }},
    {$match:{matchMe:true}}
] ).pretty()
< your matching records if any >

Answer 2

You are correct, there is some inconsistency in MongoDB's oplog implementation that allows a document format for each op log that technically does not allow for such a document to be queried accordingly.

Even inserting the same entry isn't possible as it has a $set field name:

db.tmp2.insert({ 
    "ts" : Timestamp(1450117240, 1), 
    "h" : NumberLong(2523649590228245285), 
    "v" : NumberInt(2), 
    "op" : "u", 
    "ns" : "test.tmp", 
    "o2" : {
        "_id" : ObjectId("566f069e63d6a355b2c446af")
    }, 
    "o" : {
        "$set" : {
            "b.d" : NumberInt(4)
        }
    }
})

2015-12-14T10:27:04.616-0800 E QUERY    Error: field names cannot start with $ [$set]
    at Error (<anonymous>)
    at DBCollection._validateForStorage (src/mongo/shell/collection.js:161:19)
    at DBCollection._validateForStorage (src/mongo/shell/collection.js:165:18)
    at insert (src/mongo/shell/bulk_api.js:646:20)
    at DBCollection.insert (src/mongo/shell/collection.js:243:18)
    at (shell):1:9 at src/mongo/shell/collection.js:161

and the b.d is invalid for a key

db.tmp.update({ a: 1 }, { $set: { 'b.d': 4 } }, { upsert: true })
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })

db.oplog.rs.find()


db.tmp2.insert({ 
    "ts" : Timestamp(1450117240, 1), 
    "h" : NumberLong(2523649590228245285), 
    "v" : NumberInt(2), 
    "op" : "u", 
    "ns" : "test.tmp", 
    "o2" : {
        "_id" : ObjectId("566f069e63d6a355b2c446af")
    }, 
    "o" : {
        "set" : {
            "b.d" : NumberInt(4)
        }
    }
})

2015-12-14T10:23:26.491-0800 E QUERY    Error: can't have . in field names [b.d]
    at Error (<anonymous>)
    at DBCollection._validateForStorage (src/mongo/shell/collection.js:157:19)
    at DBCollection._validateForStorage (src/mongo/shell/collection.js:165:18)
    at DBCollection._validateForStorage (src/mongo/shell/collection.js:165:18)
    at insert (src/mongo/shell/bulk_api.js:646:20)
    at DBCollection.insert (src/mongo/shell/collection.js:243:18)
    at (shell):1:9 at src/mongo/shell/collection.js:157

Perhaps a Jira issue should be logged that recommends a syntax with the $set search should be set as a value:

{ 
    "ts" : Timestamp(1450117240, 1), 
    "h" : NumberLong(2523649590228245285), 
    "v" : NumberInt(2), 
    "op" : "u", 
    "ns" : "test.tmp", 
    "o2" : {
        "_id" : ObjectId("566f069e63d6a355b2c446af")
    }, 
    "o" : {
        "$set" : {
            "key" : "b.d"
            "value" : NumberInt(4)
        }
    }
}

Update: Created a Jira issue for this:

https://jira.mongodb.org/browse/SERVER-21889