Using $not in mongodb

Question

I'm trying to do something like this :

select * from table where not (a=3 and b=4 and c=3 or x=4)

I would expect this to work:

db.table.find({
  $not: {
    $or: [
      {
        $and: [
          { a: 3 },
          { b: 4 },
          { c: 3 }
        ]
      },
      { x: 4 }
    ]
  }
})

But it gives me an error:

 error: { "$err" : "invalid operator: $and", "code" : 10068 }

Is there another way to express it in mongodb?

AdaTheDev · Accepted Answer · 2012-01-17 11:28:07Z

5

Firstly, I don't think you mean "and" as a field will never 3 and 4 at the same time - it can only be 3 or 4. So, assuming you do want "documents where b is not 3 or 4, then you can use $nin (not in) like this:

db.table.find({b:{$nin: [3,4]}});

answered Jan 17, 2012 at 11:28

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Klaus Hessellund Over a year ago

Arh.. bad example. Lets say it a bit more complex. Just edited my post. This is my first post on stackoverflow, so i'm messing around :p

Soviut · Accepted Answer · 2017-07-08 23:26:20Z

3

Using { $not : { $and : []} } will not work ($not is not like other operators, can only be applied to negate the check of other operators).

$and is not the problem here, this also doesn't work (though without reporting any errors):

{ $not : {  a : {$gt : 14} }

you'd have to rewrite it to

{ a : {  $not : {$gt : 14} }

Coming back to your query:

`not (a=3 and b=4 and c=3 or x=4)`

is equivalent to:

a!=3 and b!=4 and c!=3 or x!=4

and that you can do in mongo:

{a : { $ne : 3}, b : { $ne : 4} ...}

edited Jul 8, 2017 at 23:26

Soviut

92.2k53 gold badges210 silver badges285 bronze badges

answered Jan 17, 2012 at 12:18

milan

12.5k3 gold badges46 silver badges49 bronze badges

3 Comments

Remon van Vliet Over a year ago

I think you should write the query completely because you're now leaving out the important $or clause (which is needed)

Klaus Hessellund Over a year ago

The reason i'm asking this, is because i'm trying to make a sql to mongodb converter. Not easy for me i must say. But I think I got a step closer with the "equivalent" example. It can be found here : klaus.dk/sqltomongodb

Remon van Vliet Over a year ago

You're going to run into issues with trying to convert a relational query language to a document based on in a real hurry. Not worth the effort.

Collectives™ on Stack Overflow

Using $not in mongodb

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related