[SERVER-14201] Support $not in document match negation Created: 07/Jun/14  Updated: 10/Dec/14  Resolved: 09/Jun/14

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Querying
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Minor - P4
Reporter: Neil Lunn Assignee: Unassigned
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Participants:

 Description   

Given the example of a set of documents like this:

{ "status" : "A", "type" : "C" }
{ "status" : "A", "type" : "D" }
{ "status" : "B", "type" : "C" }
{ "status" : "B", "type" : "D" }
 

Currently if you wanted to find all documents that did not contain the combination of "status": "A" and "type": "C" you would have to resort to JavaScript evaluation.

    db.collection.find(function() {
        !( this.status == "A" && this.type == "C")        
    })

The aggregation pipeline allows such casting to a projection of a field:

 
    db.collection.aggregate([
        { "$project": {
            "status": 1,
            "type": 1,
            "unmatched": {
                "$not": {
                    "$and": [
                        { "$eq": [ "$status", "A" ] },
                        { "$eq": [ "$type", "C" ] }
                    ]
                }
            }
        }},
        { "$match": {
            "unmatched": false
        }}
    ])
{code:javascript}
 
But as there is not a native form of this for a query you could not filter for a $match stage.
 
The seemingly natural form would be the similar:
 
{code: javascript}
 
    db.collection.find({ "$not": { "status": "A", "type": "C" } })
 

Where the existing $not operator applied it's left side evaluation to the query match negating the whole expression of the query on the right side.

Not sure if there is an efficient way to apply this to index values or whether the result would be the same collection scan for all documents not matching the exclusion.

The general problem presently being that $not is not allowed as a top level operator here at the moment and cannot reverse the entire query condition as it expects a left side field value. And the JavaScript form for a $where clause is not allowed within an aggregation expression

error: {
        "$err" : "Can't canonicalize query: BadValue unknown top level operator: $not",
        "code" : 17287
}



 Comments   
Comment by Asya Kamsky [ 09/Jun/14 ]

The requested functionality can be achieved by the $or operator with negation of the equality condition:

> db.not.find()
{ "_id" : ObjectId("5394f09256ec651f2d8cce2b"), "status" : "A", "type" : "C" }
{ "_id" : ObjectId("5394f09256ec651f2d8cce2c"), "status" : "A", "type" : "D" }
{ "_id" : ObjectId("5394f09256ec651f2d8cce2d"), "status" : "B", "type" : "C" }
{ "_id" : ObjectId("5394f09256ec651f2d8cce2e"), "status" : "B", "type" : "D" }
> db.not.find({$where:'!(this.status=="A" && this.type=="C")'})
{ "_id" : ObjectId("5394f09256ec651f2d8cce2c"), "status" : "A", "type" : "D" }
{ "_id" : ObjectId("5394f09256ec651f2d8cce2d"), "status" : "B", "type" : "C" }
{ "_id" : ObjectId("5394f09256ec651f2d8cce2e"), "status" : "B", "type" : "D" }
> db.not.find({$or:[{status:{$ne:"A"}},{type:{$ne:"C"}}]})
{ "_id" : ObjectId("5394f09256ec651f2d8cce2c"), "status" : "A", "type" : "D" }
{ "_id" : ObjectId("5394f09256ec651f2d8cce2d"), "status" : "B", "type" : "C" }
{ "_id" : ObjectId("5394f09256ec651f2d8cce2e"), "status" : "B", "type" : "D" }

Comment by Stennie Steneker (Inactive) [ 09/Jun/14 ]

As per the discussion with neillunn on SERVER-14208, the $or with $ne clauses is actually working as expected in 2.6.1 and will return the desired results without resorting to a $where.

Regards,
Stephen

Comment by Neil Lunn [ 09/Jun/14 ]

Then this appears to actually be a bug in the 2.6.1 release where all documents would be returned.

Comment by Asya Kamsky [ 09/Jun/14 ]

It's "or" of negated condition. Returns everything except the first document, exactly what you "example" would (if you add $where to it).

Comment by Neil Lunn [ 09/Jun/14 ]

It's not the same logic. The examples as shown would negate the first document from the result as the query asks for the inverse of the documents that contain both status "A" and type "C". A query with $or as shown would not actually negate anything and return all of the results.

Comment by Asya Kamsky [ 08/Jun/14 ]

Why can't you use $or

db.collection.find({$or:[{status:{$ne:"A"}},{type:{$ne:"C"}}]})

Generated at Thu Feb 08 03:34:09 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.