Learning MongoDB how to find using data from 2 collections

Question

I've got 2 collections that are related. Decks and Facts.

Decks looks like this:

{
   _id: ObjectId("123456789123456789"),
   title: "test deck"
   isActive: true
}

A fact looks like this

{
   _id: ObjectId("111111324324434"),
   deckId: ObjectId("123456789123456789")
   title: "test fact"
}

I come from a SQL background so I want to SELECT * FROM facts LEFT JOIN decks ON decks._id = facts.deckId WHERE deck.isActive = true

The only way I could figure this out was via a micro script.

var deckIds = db.decks.find({isActive: false}, {_id: true}).toArray().map(function(item) {
 return item._id; 
});
db.facts.find({deckId: {$in: deckIds}});

Is this the best way to do this? Is there a more performant way to do this all in Mongo? I looked at map-reduce but I can't figure out how to do this there and it seemed like way more code. What I want to do in the end is delete the facts that have deck.isActive = false.

Possible duplicate of [How do I perform the SQL Join equivalent in MongoDB?](http://stackoverflow.com/questions/2350495/how-do-i-perform-the-sql-join-equivalent-in-mongodb) — joao, Feb 18 '16 at 14:11
My question is "Is this the best way". I don't have mongo 3.2 so I can't use $lookup. So the micro script above is the best? — Sean Clark, Feb 18 '16 at 14:16
If you are not using mongo 3.2 there are not many options. You either design your data schema so that you don't need to perform joins or you do it in the client side (like the script you posted). — joao, Feb 18 '16 at 14:20

Edson Marquezani Filho · Answer 1 · 2016-02-18T14:47:52.103

MongoDB is a NoSQL database, so you won't get native JOIN functionality. You are supposed to do that manually, as you are doing.

However, one essential thing to consider is data modeling. NoSQL models tends to be denormalized, contrary to what is considered a best practice in relational databases, which is to normalize as much as you can.

So, maybe, your fact document could include other deck's fields, for querying simplification's sake. I'm not saying this is the best option for you, but only warning you that in non relational databases, you should think different when it comes to data modeling. Off course, every model you choose has impacts on writing and reading, it depends heavily on your workload. This demands some exercising and practice, but there's some guidelines you can follow. Look at the official documentation.

There's a good book from Packt Publishing too about this topic, called MongoDB Data Modeling.

The good thing is, MongoDB is very powerful when it comes to features (you can have nested documents, arrays, in-place operations, etc), so you have many options to achieve what you need.

Also, don't forget to create indexes according to your queries and updates (the _id field is automatically indexed, the rest not).

Yea I agree, I often have to CRUD on facts and decks separately so it makes sense to keep them separate. I just wasn't sure if either map-reduce or aggregate functions could be used. — Sean Clark, Feb 18 '16 at 14:47
Map Reduce only makes sense when you have shards, when there's some aggregation you want to run in background spread through different servers. Its result will be put in another collection. It doesn't seem to be what you need. To be honest with you, I'm not very familiarized with the Mongo's Aggregation framework, but as far as I could see, it only applies to a single collection as well, so you go back to the same problem: relation between collections. — Edson Marquezani Filho, Feb 18 '16 at 15:01

score 1 · Answer 2 · answered Feb 18 '16 at 16:06

While that is the "SQL" way of thinking, Mongo does support this kind of work with $lookup (https://docs.mongodb.org/manual/reference/operator/aggregation/lookup/) added in Mongo 3.2. I built your data out, but used lower case for names; the following worked to yield a quick join.

db.facts.aggregate([{$lookup: {from: "decks", localField: "deckId", foreignField: "_id", as: "deck_data"}}]);

yields

{ "_id" : ObjectId("56c5eb065b2de3fa9f8a9d0c"), "title" : "test fact", "deckId" : ObjectId("56c5eadb5b2de3fa9f8a9d0b"), "deck_data" : [ { "_id" : ObjectId("56c5eadb5b2de3fa9f8a9d0b"), "title" : "test deck", "isActive" : true } ] }

You can apply other aggregation steps just by adding their objects to the array. I recommend $match (equivalent of a Find()) and $project (allows selection of fields).

if the goal is to delete the facts based on the field on the deckId how would you use aggregation to then perform that delete? Would you use this to get an array of Ids then do a 2nd delete with $in? — Sean Clark, Feb 18 '16 at 18:33
I have not tried this, but it looks like you can craft the collection you want through $match and $project stages, and then write it back to the same collection using $out. Reference here: https://docs.mongodb.org/manual/reference/operator/aggregation/out/ — Greg Syme, Feb 18 '16 at 18:57

Learning MongoDB how to find using data from 2 collections

2 Answers2