How to use $nin operator with objects in array

Question

I've made a simplified collection to show my problem:

  db.testcol.insert({ a: 1, msgs: [ { listname: "list1", someinfo: "info"}, { listname: "list2", someinfo: "info"}]});
db.testcol.insert({ a: 1, msgs: [ { listname: "list2", someinfo: "info"}]});
db.testcol.insert({ a: 1, msgs: [ { listname: "list3", someinfo: "info"}]});
db.testcol.insert({ a: 1, msgs: [ { listname: "list4", someinfo: "info"}]});
db.testcol.insert({ a: 1, msgs: [ ]});
db.testcol.insert({ a: 2 });

I need to get all documents from this collection which DOES NOT CONTAIN object with field listname: "list1" in array msgs, in other words I need all docs except first. I have problem with this query, I think I should use $nin operator but I can't find any example how to use it with objects. I've tried queries like this:

db.testcol.find({ "msgs.listName": { "$nin":[ "list1" ] } }).pretty()
db.testcol.find({ "msgs": { "$nin":[ { listName: "list1" } ] } }).pretty()

but all of them doen't work as i need I still can't get the right result. Thanks in advance.

Markus W Mahlberg · Accepted Answer · 2014-09-12 09:37:04Z

3

You have an quite common mistake.

In your collection, the field is named listname (note the lowercase) whereas in your query the field is name listName (note the camel case). Field names are case sensitive. so when adjusting the first query to

db.testcol.find({ "msgs.listname": { "$nin":[ "list1" ] } }).pretty()

you get the expected result.

EDIT

As @NeilLunn correctly pointed out in the comments, the better approach when ruling out a single value as per the question would be:

db.testcol.find({ "msgs.listname": { "$ne": "list1" } })

This approach is both cleaner and faster when just a single value needs to be ruled out.

All kudos to him.

edited Sep 12, 2014 at 9:37

answered Sep 12, 2014 at 8:52

Markus W Mahlberg

21k6 gold badges70 silver badges97 bronze badges

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

4 Comments

Dmitry Malugin Over a year ago

Thanks it a really stupid mistake i thought problem was with query(((

Neil Lunn Over a year ago

@DmitryMalugin While the other answer given here was incorrect in it's assumptions there was one valid point not addressed. You don't need operators like $nin or $in to work on an array element within a document. Simply db.testcol.find({ "msgs.listname": { "$ne": "list1" } }) is sufficient. $nin and $in are meant to be supplied an "array of arguments" themselves. So it it an almost useless use of the operator with a single argument. The "array" applies to arguments, not the content of the document.

Markus W Mahlberg Over a year ago

@NeilLunn: Right you are, as always. Especially since $nin is much worth performance wise.

Dmitry Malugin Over a year ago

Thank a lot guys, I'll follow your advises

Ivan Misic · Accepted Answer · 2014-09-12 08:51:13Z

-2

db.testcol.find({'msgs.0.listname':{'$ne':'list1'}})

answered Sep 12, 2014 at 8:51

Ivan Misic

1945 bronze badges

2 Comments

Sergio Tulentsev Over a year ago

This query is not very robust, is it?

Ivan Misic Over a year ago

Sometimes speed is better than robustness. Thanks for the -

Collectives™ on Stack Overflow

How to use $nin operator with objects in array

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related