MongoDB Shell: Error with "$or" query and explain()

Question

I'm trying to do some profiling of a MongoDB "$or" query and I'm having trouble with the 'explain()' command in the Mongo shell. When I run my query with the find command, it works and returns one record, as expected. When I tack on an 'explain()' at the end of the find() however, I get the following error:

uncaught exception: error: { "$err" : "invalid operator: $or", "code" : 10068 }

I'm trying the following query:

db.people.find({"query" : 
                   {"$or" : [ 
                       {"site" : "mysite", "site_id" : "587125722"}, 
                       {"user_id" : ObjectId("4fb811aae4b0b628b485a58d")}
                   ]}, 
                "$orderby" : { "global_id" : 1, "user_id" : 1}
                })

If I change the "$or" to "or", explain() works fine, but the query returns 0 results. I was under the impression that they syntax should be the same with or without explain(), so what am I doing wrong? I'm using version 2.0.4. Thanks to anyone who may be able to help.

score 6 · Accepted Answer · answered Jul 25 '12 at 01:47

The keyword "query" is used internally to package up the "query" (specification of what to search for) portion of a "query" (request to the server to do a search) to keep it distinct within the BSON document sent to the server, but the shell helpers (such as .explain) force this "packaging" to happen to whatever you specify. You're losing your desired query (in which you used "query" directly) by getting it wrapped up inside a "query" itself.

Matt's answer avoids this by unwrapping your pre-wrapped request, and is the normal way of doing this ... just add ".explain()" to the end of Matt's rewrite and it should work as you expect.

If you want to stay with your format, you can just add "$explain : true" to your document the same way you used "$orderby" instead of ".sort".

db.people.find({"query" : 
                   {"$or" : [ 
                       {"site" : "mysite", "site_id" : "587125722"}, 
                       {"user_id" : ObjectId("4fb811aae4b0b628b485a58d")}
                   ]}, 
                "$orderby" : { "global_id" : 1, "user_id" : 1},
                "$explain" : true
                })

Look at the JavaScript code for the shell helper ".explain" to see what it does:

> db.foo.find().explain
function (verbose) {
    var n = this.clone();
    n._ensureSpecial();
    n._query.$explain = true;
    n._limit = Math.abs(n._limit) * -1;
    var e = n.next();

    function cleanup(obj) {
        if (typeof obj != "object") {
            return;
        }
        delete obj.allPlans;
        delete obj.oldPlan;
        if (typeof obj.length == "number") {
            for (var i = 0; i < obj.length; i++) {
                cleanup(obj[i]);
            }
        }
        if (obj.shards) {
            for (var key in obj.shards) {
                cleanup(obj.shards[key]);
            }
        }
        if (obj.clauses) {
            cleanup(obj.clauses);
        }
    }

    if (!verbose) {
        cleanup(e);
    }
    return e;
}
> db.foo.find()._ensureSpecial
function () {
    if (this._special) {
        return;
    }
    var n = {query:this._query};
    this._query = n;
    this._special = true;
}
>

omg, this totally worked. I had the same issue except I was using .count( ) and it was failing for the exact same reason. Unpackaging the query the way you and Matt specified worked exactly. Thanks! — John Q, Jan 08 '17 at 07:42

score 1 · Answer 2 · answered Jul 24 '12 at 22:59

You're probably mixing up runCommand() and find(). Try this:

db.people.find( { "$or" : [ { site : "mysite", site_id : "587125722" }, 
                            { user_id : ObjectId("4fb811aae4b0b628b485a58d") } ] }
              ).sort( { global_id : 1, user_id : 1 } )

MongoDB Shell: Error with "$or" query and explain()

2 Answers2