CODEWITHSUNDEEP
javascriptnode.jsmongodbmongodb-query
kjonsson
kjonsson

Reputation: 2799

How to find by pattern but exclude a list of condtions

Hi I'm a bit stuck with a Mongo/Mongoose query that I need help with.

I have a an array of objects called friends (created fake data for example):

friends:

[
   {username: "Anders", age: 10}, 
   {username: "Bella", age: 21}, 
   {username: "Debbie", age: 22}
]

I also have a table within Mongo that I want to query called people:

people:

   [
      {username: "Anders", age: 10}, 
      {username: "Andrew", age: 20}, 
      {username: "Annmarie", age: 28}, 
      {username: "Bella", age: 21},
      {username: "Carl", age: 40}, 
      {username: "Debbie", age: 22}
   ]

My problem is that I want to retrieve all the documents from people where the users name starts with "An" but exclude the documents that appear in the friends array. I don't want to search for all documents with usernames starting with "An" and then filter out afterwards because my database is quite large.

Upvotes: 0

Views: 73

Answers (2)

Markus W Mahlberg
Markus W Mahlberg

Reputation: 20703

A different approach than the one mentioned by @NeilLunn might be remodeling the data to contain all people and distinct their relation with a field like

   {username: "Anders", age: 10, tags: ['friend']}, 
   {username: "Andrew", age: 20}, 
   {username: "Annmarie", age: 28}, 
   {username: "Bella", age: 21, tags : ['friend']},
   {username: "Carl", age: 40}, 
   {username: "Debbie", age: 22, tags : ['friend']}

and do a query like 

db.people.find({username:{$regex:"^An"},tags:{$nin:['friends']})

This approach has several advantages

  1. You don't need a collection for every type of people, making aggregations and queries concerning all persons much easier
  2. Adding a new type (relatives, coworkers) doesn't make you to create a new collection and according indices
  3. It reduces redundancy and overhead.
  4. For this special use case, it would reduce your umber of queries, since you would not have to load everybody from friends to an array and exclude them in a second query.

Upvotes: 0

Neil Lunn
Neil Lunn

Reputation: 151132

There is a $nor operator that you can apply to your list of friends to make sure all are excluded from the result. The other part is a simple $regex operator to match the names beginning with "An":

var friends = [
   {username: "Anders", age: 10}, 
   {username: "Bella", age: 21}, 
   {username: "Debbie", age: 22}
];

var query = { "$nor": friends, "username": { "$regex": "^An" } };

Your most efficient index here is clearly on "username" and "age", and note that anchoring the "regex" here is the only thing that will work efficiently for you.

Upvotes: 1

Related Questions