How to specify a MongoDB query with no results ("WHERE 0=1")

Question

I'm working on a software that translates queries in the business domain into MongoDB query documents.

For this I need somethink like a "WHERE 0==1" clause for MongoDB, i.e. a query document that can be combined with $and and $or operators and behaves like a logical false and can be optimized by the mongo query engine.

This way, if some subpart of the query builder detects an "impossible" condition, it could just return this "false" query document and let the mongodb engine ignore the respective branch of the query syntax tree.

{$where: "false"} does the trick, but it seems that in this case the mongoDB engine evaluates "false" on every row of the result (instead of simply returning an empty result set).

I also came up with{_id: {$exists: false}}, but an explain() shows that the query still uses an index scan.

So I'm curious if there's any other option to obtain an empty result set.

Deeper Explanation

The user of my software will be able to define queries in a domain specific query language, which will be translated into a rather complex mongodb query document. This query will basically have the form of {$and: [ {$or: [ {},{$and: ...},...]}]}, so this is a deeply nested, rather complex tree of conditions.

The construction of the tree is currently straightforward, since every element of the domain query translates more or less nicely into a corresponding Mongo query.

If there's one condition deep down the tree where the my software can decide on its own that it's always false I'd like to make this as explicit as possible to the Mongo query engine. So for instance, if all the "intermediate" documents consist of $and operators (and thus the overal outcome of the query would be always "false"), I want the mongo engine to detect this as quickly as possible.

If I do this in SQL, the equivalent would be something like

SELECT  * FROM TABLE WHERE C1 AND C2 AND (C3 OR C4) AND ... AND (1=0)

When the SQL engine gets this query, it doesn't need to consult any index or table at all, since the condition can be "proven" to always be false.

Your question is hard to understand. Can you please show sample document with the expected result? — styvane, Oct 24 '16 at 16:10
@Styvane Sorry for my english ;) I tried to add some additional explanation. Basically my queries get constructed automatically, and I'm looking for some way to allow Mongo to optimize away "impossible" conditions... — MartinStettner, Oct 24 '16 at 17:49

score 1 · Answer 1 · answered Sep 13 '19 at 17:34

1

With version 3.6, you can express this directly:

{ $expr: { $eq: [0, 1] } }

answered Sep 13 '19 at 17:34

chowey

9,138
6
54
84

score 0 · Answer 2 · answered Oct 24 '16 at 16:57

An index scan is going to be one of the fastest operations you can see in your explain query. One option to make this even faster would be to create a sparse index and then force your query to use that index using hints.

So index creation:

db.myCollection.createIndex( { "does_not_exist": 1 }, { sparse: true })

query:

db.myCollection.find(
    {  "does_not_exist": 
          { $exists: true }, 
       $hint: 
          {does_not_exist : 1}
    })

I have not tested this myself, so you may have to test it a bit on your own to see if this will work for your needs, but I suspect this is a path worth exploring.

score 0 · Answer 3 · edited May 23 '17 at 10:27

First of all you should use the $where operator only and only if you problem can't be solved using any other query operator. But with my little experience, if $where can do it then $redact will do it better, unless you have a very bad schema design or are doing something you shouldn't in your query expression.

In this post, I explained why one should use $redact instead of $where.

That being said for what I understood, you can do this using the $redact operator like this:

db.collection.aggregate([
    { "$redact": { 
        "$cond": [ 
            { "$eq": [true, false]}, 
            "$$KEEP", 
            "$$PRUNE"
        ]
    }}
 ])

The $$KEEP and $$PRUNE variables are a system variables that allow you to "keep" or exclude all the documents based on the retuned value of the $conditional expression.

How to specify a MongoDB query with no results ("WHERE 0=1")

3 Answers3