how do I do 'not-in' operation in mongodb?

Question

I have two collections - shoppers (everyone in shop on a given day) and beach-goers (everyone on beach on a given day). There are entries for each day, and person can be on a beach, or shopping or doing both, or doing neither on any day. I want to now do query - all shoppers in last 7 days who did not go to beach.

I am new to Mongo, so it might be that my schema design is not appropriate for nosql DBs. I saw similar questions around join and in most cases it was suggested to denormalize. So one solution, I could think of is to create collection - activity, index on date, embed actions of user. So something like

{
   user_id
   date
   actions {
      [action_type, ..]
   }
}

Insertion now becomes costly, as now I will have to query before insert.

Answer 1

A few of suggestions.

Figure out all the queries you'll be running, and all the types of data you will need to store. For example, do you expect to add activities in the future or will beach and shop be all?

Consider how many writes vs. reads you will have and which has to be faster.

Determine how your documents will grow over time to make sure your schema is scalable in the long term.

Here is one possible approach, if you will only have these two activities ever. One record per user per day.

{ user: "user1",
  date: "2012-12-01",
  shopped: 0,
  beached: 1
}

Now your query becomes even simpler, whether you have two or ten activities.

When new activity comes in you always have to update the correct record based on it. If you were thinking you could just append a record to your collection indicating user, date, activity then your inserts are much faster but your queries now have to do a LOT of work querying for both users, dates and activities.

With proposed schema, here is the insert/update statement:

db.coll.update({"user":"username", "date": "somedate"}, {"shopped":{$inc:1}}, true)

What that's saying is: "for username on somedate increment their shopped attribute by 1 and create it if it doesn't exist aka "upsert" (that's the last 'true' argument).

Here is the query for all users on a particular day who did activity1 more than once but didn't do any of activity2.

db.coll.find({"date":"somedate","shopped":0,"danced":{$gt:1}})

Be wary of picking a schema where a single document can have continuous and unbounded growth.

For example, storing everything in a users collection where the array of dates and activities keeps growing will run into this problem. See the highlighted section here for explanation of this - and keep in mind that large documents will keep getting into your working data set and if they are huge and have a lot of useless (old) data in them, that will hurt the performance of your application, as will fragmentation of data on disk.

Remember, you don't have to put all the data into a single collection. It may be best to have a users collection with a fixed set of attributes of that user where you track how many friends they have or other semi-stable information about them and also have a user_activity collection where you add records for each day per user what activities they did. The amount or normalizing or denormalizing of your data is very tightly coupled to the types of queries you will be running on it, which is why figure out what those are is the first suggestion I made.

Answer 2

Insertion now becomes costly, as now I will have to query before insert.

Keep in mind that even with RDBMS, insertion can be (relatively) costly when there are indices in place on the table (ie, usually). I don't think using embedded documents in Mongo is much different in this respect.

For the query, as Asya Kamsky suggest you can use the $nin operator to find everyone who didn't go to the beach. Eg:

db.people.find({ 
    actions: { $nin: ["beach"] }
});

Using embedded documents probably isn't the best approach in this case though. I think the best would be to have a "flat" activities collection with documents like this:

{
    user_id
    date
    action
}

Then you could run a query like this:

var start = new Date(2012, 6, 3);
var end = new Date(2012, 5, 27);
db.activities.find({ 
    date: {$gte: start, $lt: end }, 
    action: { $in: ["beach", "shopping" ] } 
});

The last step would be on your client driver, to find user ids where records exist for "shopping", but not for "beach" activities.

Answer 3

One possible structure is to use an embedded array of documents (a users collection):

{
    user_id: 1234,
    actions: [ 
        { action_type: "beach", date: "6/1/2012" },
        { action_type: "shopping", date: "6/2/2012" }
    ]
},
{ another user }

Then you can do a query like this, using $elemMatch to find users matching certain criteria (in this case, people who went shopping in the last three days:

var start = new Date(2012, 6, 1);
db.people.find( { 
    actions : { 
        $elemMatch : { 
            action_type : { $in: ["shopping"] }, 
            date : { $gt : start } 
        } 
    } 
});

Expanding on this, you can use the $and operator to find all people went shopping, but did not go to the beach in the past three days:

var start = new Date(2012, 6, 1);
db.people.find( {  
    $and: [
        actions : { 
            $elemMatch : { 
                action_type : { $in: ["shopping"] }, 
                date : { $gt : start } 
            } 
        },
        actions : { 
            $not: {
                $elemMatch : { 
                    action_type : { $in: ["beach"] }, 
                    date : { $gt : start } 
                } 
            }
        }
    ]
});