Mongoose Inner Join

Question

user collection

user : {
"_id" : md5random,
"nickname" : "j1",
"name" : "jany"
}
user : {
"_id" : md5random,
 "nickname" : "j2",
"name" : "jenneffer"
}

friendship collection

friendship : {
  "_id"  : md5rand,
  "nick1" : "j1",
  "nick2"  : "j2",
  "adTime" : date
}

for example SQL

SELECT friendship.adTime, user.name 
         FROM friendship
INNER JOIN user  ON 
        (user.nickname=friendship.nick1 or user.nickname=friendship.nick2)

Is there any way in the MONGO to get this SQL result?

I WANT GET RESULTS, i know mongo not supporting this request. But what is this better solution? Any body can write an exeample for me?

Answer 1

MongoDB is n document-oriented, not a relational database. So it doesn't have a query language like sql. Therefore you should change you database schema.

Here is a example:

1. Define your schema. You can't save relations in mongodb like in sql databases. so add the friends directly to the user. to add attributes to these relations you have to create a new model (here: Friend).

   var userSchema = mongoose.Schema({
       nickname: String,
       name: String,
       friends: [{type: mongoose.Schema.Types.ObjectId, ref: 'Friend'}]
   });
   
   var friendSchema = mongoose.Schema({
       user: {type: mongoose.Schema.Types.ObjectId, ref: 'User'},
       addTime: Date
   });
   
   var User = mongoose.Model('User', userSchema);
   var Friend = mongoose.Model('Friend', friendSchema);
   

Your query to get all addTimes's could look like this:

User.find().populate('friends').exec(function(err, users) {
    if (err) throw err;

    var adTimes = [];
    users.forEach(function(user) {
        user.friends.forEach(function(friend) {
            adTimes.push(friend.adTime);
        });
    });

    response.send(adTimes); // adTimes should contain all addTimes from his friends
});

NOTE: The above schema should work, but maybe you should use a relational (like MySQL) or graph database (like Neo4j) instead of a document-oriented like MongoDB.

Answer 2

Use $lookup, Performs a left outer join to an unsharded collection in the same database to filter in documents from the “joined” collection for processing. The $lookup stage does an equality match between a field from the input documents with a field from the documents of the “joined” collection.

To each input document, the $lookup stage adds a new array field whose elements are the matching documents from the “joined” collection. The $lookup stage passes these reshaped documents to the next stage.

For more detail click $lookup documentation