How do I perform the SQL Join equivalent in MongoD

Question

How do I perform the SQL Join equivalent in MongoDB?

For example say you have two collections (users and comments) and I want to pull all the comments with pid=444 along with the user info for each.

comments
  { uid:12345, pid:444, comment="blah" }
  { uid:12345, pid:888, comment="asdf" }
  { uid:99999, pid:444, comment="qwer" }

users
  { uid:12345, name:"john" }
  { uid:99999, name:"mia"  }

Is there a way to pull all the comments with a certain field (eg. ...find({pid:444}) ) and the user information associated with each comment in one go?

At the moment, I am first getting the comments which match my criteria, then figuring out all the uid's in that result set, getting the user objects, and merging them with the comment's results. Seems like I am doing it wrong.

Answer 1

You can do it using the aggregation pipeline, but it's a pain to write it yourself.

You can use mongo-join-query to create the aggregation pipeline automatically from your query.

This is how your query would look like:

const mongoose = require("mongoose");
const joinQuery = require("mongo-join-query");

joinQuery(
    mongoose.models.Comment,
    {
        find: { pid:444 },
        populate: ["uid"]
    },
    (err, res) => (err ? console.log("Error:", err) : console.log("Success:", res.results))
);

Your result would have the user object in the uid field and you can link as many levels deep as you want. You can populate the reference to the user, which makes reference to a Team, which makes reference to something else, etc..

Disclaimer: I wrote mongo-join-query to tackle this exact problem.

Answer 2

It depends on what you're trying to do.

You currently have it set up as a normalized database, which is fine, and the way you are doing it is appropriate.

However, there are other ways of doing it.

You could have a posts collection that has imbedded comments for each post with references to the users that you can iteratively query to get. You could store the user's name with the comments, you could store them all in one document.

The thing with NoSQL is it's designed for flexible schemas and very fast reading and writing. In a typical Big Data farm the database is the biggest bottleneck, you have fewer database engines than you do application and front end servers...they're more expensive but more powerful, also hard drive space is very cheap comparatively. Normalization comes from the concept of trying to save space, but it comes with a cost at making your databases perform complicated Joins and verifying the integrity of relationships, performing cascading operations. All of which saves the developers some headaches if they designed the database properly.

With NoSQL, if you accept that redundancy and storage space aren't issues because of their cost (both in processor time required to do updates and hard drive costs to store extra data), denormalizing isn't an issue (for embedded arrays that become hundreds of thousands of items it can be a performance issue, but most of the time that's not a problem). Additionally you'll have several application and front end servers for every database cluster. Have them do the heavy lifting of the joins and let the database servers stick to reading and writing.

TL;DR: What you're doing is fine, and there are other ways of doing it. Check out the mongodb documentation's data model patterns for some great examples. http://docs.mongodb.org/manual/data-modeling/

Answer 3

MongoDB does not allow joins, but you can use plugins to handle that. Check the mongo-join plugin. It's the best and I have already used it. You can install it using npm directly like this npm install mongo-join. You can check out the full documentation with examples.

(++) really helpful tool when we need to join (N) collections

(--) we can apply conditions just on the top level of the query

Example

var Join = require('mongo-join').Join, mongodb = require('mongodb'), Db = mongodb.Db, Server = mongodb.Server;
db.open(function (err, Database) {
    Database.collection('Appoint', function (err, Appoints) {

        /* we can put conditions just on the top level */
        Appoints.find({_id_Doctor: id_doctor ,full_date :{ $gte: start_date },
            full_date :{ $lte: end_date }}, function (err, cursor) {
            var join = new Join(Database).on({
                field: '_id_Doctor', // <- field in Appoints document
                to: '_id',         // <- field in User doc. treated as ObjectID automatically.
                from: 'User'  // <- collection name for User doc
            }).on({
                field: '_id_Patient', // <- field in Appoints doc
                to: '_id',         // <- field in User doc. treated as ObjectID automatically.
                from: 'User'  // <- collection name for User doc
            })
            join.toArray(cursor, function (err, joinedDocs) {

                /* do what ever you want here */
                /* you can fetch the table and apply your own conditions */
                .....
                .....
                .....


                resp.status(200);
                resp.json({
                    "status": 200,
                    "message": "success",
                    "Appoints_Range": joinedDocs,


                });
                return resp;


            });

    });

Answer 4

Nope, it doesn't seem like you're doing it wrong. MongoDB joins are "client side". Pretty much like you said:

At the moment, I am first getting the comments which match my criteria, then figuring out all the uid's in that result set, getting the user objects, and merging them with the comment's results. Seems like I am doing it wrong.

1) Select from the collection you're interested in.
2) From that collection pull out ID's you need
3) Select from other collections
4) Decorate your original results.

It's not a "real" join, but it's actually alot more useful than a SQL join because you don't have to deal with duplicate rows for "many" sided joins, instead your decorating the originally selected set.

There is alot of nonsense and FUD on this page. Turns out 5 years later MongoDB is still a thing.

Answer 5

Here's an example of a "join" * Actors and Movies collections:

https://github.com/mongodb/cookbook/blob/master/content/patterns/pivot.txt

It makes use of .mapReduce() method

* join - an alternative to join in document-oriented databases

Answer 6

Before 3.2.6, Mongodb does not support join query as like mysql. below solution which works for you.

 db.getCollection('comments').aggregate([
        {$match : {pid : 444}},
        {$lookup: {from: "users",localField: "uid",foreignField: "uid",as: "userData"}},
   ])