How do I perform the SQL Join equivalent in MongoDB?

mongodb join multiple collections
join two collections in mongodb using java
mongodb join two collections example
join two collections in mongodb using node js
mongodb self join
mongodb lookup pipeline: ( ( $match))
mongodb lookup example
nested lookup mongodb

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.

As of Mongo 3.2 the answers to this question are mostly no longer correct. The new $lookup operator added to the aggregation pipeline is essentially identical to a left outer join:

https://docs.mongodb.org/master/reference/operator/aggregation/lookup/#pipe._S_lookup

From the docs:

{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}

Of course Mongo is not a relational database, and the devs are being careful to recommend specific use cases for $lookup, but at least as of 3.2 doing join is now possible with MongoDB.

Using JOINs in MongoDB NoSQL Databases, In relational databases, the SQL JOIN clause allows you to combine MongoDB 3.2 introduces a new $lookup operator which can perform a  Using JOINs in MongoDB NoSQL Databases Thanks to Julian Motz for kindly helping to peer review this article. One of the biggest differences between SQL and NoSQL databases is JOIN.

This page on the official mongodb site addresses exactly this question:

http://docs.mongodb.org/ecosystem/tutorial/model-data-for-ruby-on-rails/

When we display our list of stories, we'll need to show the name of the user who posted the story. If we were using a relational database, we could perform a join on users and stores, and get all our objects in a single query. But MongoDB does not support joins and so, at times, requires bit of denormalization. Here, this means caching the 'username' attribute.

Relational purists may be feeling uneasy already, as if we were violating some universal law. But let’s bear in mind that MongoDB collections are not equivalent to relational tables; each serves a unique design objective. A normalized table provides an atomic, isolated chunk of data. A document, however, more closely represents an object as a whole. In the case of a social news site, it can be argued that a username is intrinsic to the story being posted.

3 Approaches to Creating a SQL-Join Equivalent in MongoDB, Learn about a simple function for joining data within a single MongoDB collection and ensuring that MongoDB apps integrate with your  mongodb lookup pipeline (13) . 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.

We can merge/join all data inside only one collection with a easy function in few lines using the mongodb client console, and now we could be able of perform the desired query. Below a complete example,

.- Authors:

db.authors.insert([
    {
        _id: 'a1',
        name: { first: 'orlando', last: 'becerra' },
        age: 27
    },
    {
        _id: 'a2',
        name: { first: 'mayra', last: 'sanchez' },
        age: 21
    }
]);

.- Categories:

db.categories.insert([
    {
        _id: 'c1',
        name: 'sci-fi'
    },
    {
        _id: 'c2',
        name: 'romance'
    }
]);

.- Books

db.books.insert([
    {
        _id: 'b1',
        name: 'Groovy Book',
        category: 'c1',
        authors: ['a1']
    },
    {
        _id: 'b2',
        name: 'Java Book',
        category: 'c2',
        authors: ['a1','a2']
    },
]);

.- Book lending

db.lendings.insert([
    {
        _id: 'l1',
        book: 'b1',
        date: new Date('01/01/11'),
        lendingBy: 'jose'
    },
    {
        _id: 'l2',
        book: 'b1',
        date: new Date('02/02/12'),
        lendingBy: 'maria'
    }
]);

.- The magic:

db.books.find().forEach(
    function (newBook) {
        newBook.category = db.categories.findOne( { "_id": newBook.category } );
        newBook.lendings = db.lendings.find( { "book": newBook._id  } ).toArray();
        newBook.authors = db.authors.find( { "_id": { $in: newBook.authors }  } ).toArray();
        db.booksReloaded.insert(newBook);
    }
);

.- Get the new collection data:

db.booksReloaded.find().pretty()

.- Response :)

{
    "_id" : "b1",
    "name" : "Groovy Book",
    "category" : {
        "_id" : "c1",
        "name" : "sci-fi"
    },
    "authors" : [
        {
            "_id" : "a1",
            "name" : {
                "first" : "orlando",
                "last" : "becerra"
            },
            "age" : 27
        }
    ],
    "lendings" : [
        {
            "_id" : "l1",
            "book" : "b1",
            "date" : ISODate("2011-01-01T00:00:00Z"),
            "lendingBy" : "jose"
        },
        {
            "_id" : "l2",
            "book" : "b1",
            "date" : ISODate("2012-02-02T00:00:00Z"),
            "lendingBy" : "maria"
        }
    ]
}
{
    "_id" : "b2",
    "name" : "Java Book",
    "category" : {
        "_id" : "c2",
        "name" : "romance"
    },
    "authors" : [
        {
            "_id" : "a1",
            "name" : {
                "first" : "orlando",
                "last" : "becerra"
            },
            "age" : 27
        },
        {
            "_id" : "a2",
            "name" : {
                "first" : "mayra",
                "last" : "sanchez"
            },
            "age" : 21
        }
    ],
    "lendings" : [ ]
}

I hope this lines can help you.

How to Create MongoDB Joins Using SQL, Performs a left outer join to an unsharded collection in the same database to filter in The operation would correspond to the following pseudo-SQL statement:. MongoDB join, MongoDB aggregate, MongoDB lookup, MongoDB Interview Question

You have to do it the way you described. MongoDB is a non-relational database and doesn't support joins.

$lookup (aggregation), Integrating MongoDB document data with SQL and other table-centric data sources needn't be so processor-intensive. TL;DR: While there's no  comment réaliser L'équivalent SQL Join en MongoDB? par exemple dire que vous avez deux collections (utilisateurs et commentaires) et je veux tirer tous les commentaires avec pid=444 avec les informations utilisateur pour chacun.

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

Three Approaches to Creating a SQL-Join Equivalent in MongoDB , Join Collections. MongoDB is not a relational database, but you can perform a left outer join by using the $lookup stage. The $lookup stage lets you specify  The SQL query is validated and translated into a MongoDB query and executed by NoSQLBooster for MongoDB. The Equivalent MongoDB Query can be viewed in the console.log tab. It should be mentioned that there is a tutorial on NoSQLBooster SQL Query for MongoDB in the lower left “Samples” pane.

Node.js MongoDB Join, Lookup, the MongoDB equivalent to Joins, cannot yet do Merge joins or hash to do an index nested loops join, but beyond that we have difficulties in getting such a MongoDB database perform reasonably when reporting historic and We execute the following SQL query in MongoDB using Studio 3T,  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 eac

Enjoying Joins in MongoDB - Simple Talk, While there's no such operation as a SQL-style table join in MongoDB, you can achieve the same effect without relying on table schema. Here are three  If you are considering migrating your SQL application to MongoDB, download the MongoDB Application Modernization Guide. The download includes the following resources: Presentation on the methodology of data modeling with MongoDB; White paper covering best practices and considerations for migrating to MongoDB from an RDBMS data model

How to perform the SQL Join equivalent in MongoDB, In SQL databases, we can perform a JOIN between two tables within the same or The equivalent MongoDB code you will get in this case is: ? With an ODBC MongoDB driver from a reputable source, you can do the same thing: talk in SQL. There is a handy chart provided by MongoDB that shows the equivalence between the two query languages, the SQL to MongoDB Mapping Chart .

Comments
  • The last answer on this question is probably the most relevant, since MongoDB 3.2+ implemented a join solution called $lookup. Thought I would push it here because maybe not everyone will read to the bottom. stackoverflow.com/a/33511166/2593330
  • Correct, $lookup was introduced in MongoDB 3.2. Details can be found at docs.mongodb.org/master/reference/operator/aggregation/lookup/…
  • i wonder why do not accept this anwser or any comment: stackoverflow.com/a/33511166/6941294
  • @clayton : How about more then two collections?
  • @DipenDedania just add additional $lookup stages to the aggregation pipeline.
  • I cant join any field in array in left collection with its corresponding id in right collection.can anybody help me??
  • I'm a little confused about this - is there any way to specify that you want only certain documents in the "from" collection, or does it automatically join all in the db at once?
  • Just wondering if the latest Spring Data MongoDB has support for 3.2?
  • @dudelgrincen it's a paradigm shift from normalization and relational databases. The goal of a NoSQL is to read and write from the database very quickly. With BigData you're going to have scads of application and front end servers with lower numbers on DBs. You're expected to do millions of transactions a second. Offload the heavy lifting from the database and put it onto the application level. If you need deep analysis, you run an integration job that puts your data into an OLAP database. You shouldn't be getting many deep queries from your OLTP dbs anyway.
  • @dudelgrincen I should also say that it's not for every project or design. If you have something that works in a SQL type database why change it? If you can't massage your schema to work with noSQL, then don't.