How do I perform the SQL Join equivalent in MongoDB?












436















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.










share|improve this question




















  • 30





    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

    – thefourtheye
    Nov 22 '15 at 16:33






  • 6





    Correct, $lookup was introduced in MongoDB 3.2. Details can be found at docs.mongodb.org/master/reference/operator/aggregation/lookup/…

    – NDB
    Nov 23 '15 at 13:20











  • i wonder why do not accept this anwser or any comment: stackoverflow.com/a/33511166/6941294

    – Nozar Safari
    Dec 18 '17 at 6:26
















436















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.










share|improve this question




















  • 30





    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

    – thefourtheye
    Nov 22 '15 at 16:33






  • 6





    Correct, $lookup was introduced in MongoDB 3.2. Details can be found at docs.mongodb.org/master/reference/operator/aggregation/lookup/…

    – NDB
    Nov 23 '15 at 13:20











  • i wonder why do not accept this anwser or any comment: stackoverflow.com/a/33511166/6941294

    – Nozar Safari
    Dec 18 '17 at 6:26














436












436








436


123






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.










share|improve this 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.







mongodb join






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Feb 22 at 8:00









philipxy

11.8k42353




11.8k42353










asked Feb 28 '10 at 8:15









The UnknownThe Unknown

8,439276590




8,439276590








  • 30





    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

    – thefourtheye
    Nov 22 '15 at 16:33






  • 6





    Correct, $lookup was introduced in MongoDB 3.2. Details can be found at docs.mongodb.org/master/reference/operator/aggregation/lookup/…

    – NDB
    Nov 23 '15 at 13:20











  • i wonder why do not accept this anwser or any comment: stackoverflow.com/a/33511166/6941294

    – Nozar Safari
    Dec 18 '17 at 6:26














  • 30





    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

    – thefourtheye
    Nov 22 '15 at 16:33






  • 6





    Correct, $lookup was introduced in MongoDB 3.2. Details can be found at docs.mongodb.org/master/reference/operator/aggregation/lookup/…

    – NDB
    Nov 23 '15 at 13:20











  • i wonder why do not accept this anwser or any comment: stackoverflow.com/a/33511166/6941294

    – Nozar Safari
    Dec 18 '17 at 6:26








30




30





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

– thefourtheye
Nov 22 '15 at 16:33





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

– thefourtheye
Nov 22 '15 at 16:33




6




6





Correct, $lookup was introduced in MongoDB 3.2. Details can be found at docs.mongodb.org/master/reference/operator/aggregation/lookup/…

– NDB
Nov 23 '15 at 13:20





Correct, $lookup was introduced in MongoDB 3.2. Details can be found at docs.mongodb.org/master/reference/operator/aggregation/lookup/…

– NDB
Nov 23 '15 at 13:20













i wonder why do not accept this anwser or any comment: stackoverflow.com/a/33511166/6941294

– Nozar Safari
Dec 18 '17 at 6:26





i wonder why do not accept this anwser or any comment: stackoverflow.com/a/33511166/6941294

– Nozar Safari
Dec 18 '17 at 6:26












19 Answers
19






active

oldest

votes


















257














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.






share|improve this answer





















  • 11





    is $lookup performance-wise the better option?

    – Mohsen Shakiba
    Nov 21 '15 at 9:06






  • 5





    Agreed. This is the best answer.

    – NDB
    Nov 23 '15 at 13:21











  • @clayton : How about more then two collections?

    – Dipen Dedania
    Jan 8 '16 at 10:36






  • 1





    @DipenDedania just add additional $lookup stages to the aggregation pipeline.

    – Clayton Gulick
    Jan 9 '16 at 1:01











  • I cant join any field in array in left collection with its corresponding id in right collection.can anybody help me??

    – Prateek Singh
    Feb 1 '16 at 16:52



















136














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.







share|improve this answer





















  • 114





    Then why so many people love MongoDB. I dont get it :|

    – Boris D. Teoharov
    Sep 15 '13 at 15:47






  • 48





    @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.

    – Snowburnt
    Nov 4 '13 at 1:53






  • 17





    @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.

    – Snowburnt
    Nov 12 '13 at 0:30






  • 9





    Migrations and a constantly evolving schemas are also a lot easier to manage on a NoSQL system.

    – justin
    May 6 '14 at 20:09






  • 11





    What if the user has 3.540 posts in the website, and he does change his username in profile? Should every post be updated with the new username?

    – Ivo Pereira
    Mar 2 '16 at 17:39



















130














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.






share|improve this answer



















  • 2





    i'm wondering if this same code can be ran using doctrine mongodb?

    – abbood
    May 30 '14 at 13:46






  • 4





    What happens when one of the references objects gets an update? Does that update automatically reflect in the book object? Or does that loop need to run again?

    – balupton
    Jun 4 '14 at 5:55






  • 9





    This is fine as long as your data is small. It is going to bring each book content to your client and then fetch each category, lending and authors one by one. The moment your books are in thousands, this would go really really slow. A better technique probably would be to use aggregation pipeline and output the merged data into a separate collection. Let me get back to it again. I will add that an answer.

    – Sandeep Giri
    Jun 19 '14 at 15:31













  • Can you adapt your algorithm to this other example? stackoverflow.com/q/32718079/287948

    – Peter Krauss
    Sep 22 '15 at 14:13






  • 1





    @SandeepGiri how can i do the aggregate pipeline since i have really really intensive data in separated collection need join ??

    – Yassine Abdul-Rahman
    Oct 7 '15 at 20:16



















37














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






share|improve this answer



















  • 4





    Seems wrong performance wise coming from a sql server background, but its maybe not that bad with a document db?

    – terjetyl
    Jul 15 '10 at 18:20






  • 3





    from a sql server background as well, I would appreciate MongoDB taking a 'result set' (with selected returned fields) as input for a new query in one go, much like nested queries in SQL

    – Stijn Sanders
    Nov 26 '10 at 23:17






  • 1





    @terjetyl You have to really plan for it. What fields are you going be presenting on the front end, if it's a limited amount in an individual view then you take those as embedded documents. The key is to not need to do joins. If you want to do deep analysis, you do it after the fact in another database. Run a job that transforms the data into an OLAP cube for optimal performance.

    – Snowburnt
    Nov 4 '13 at 1:56






  • 3





    From mongo 3.2 version left joins are supported.

    – Somnath Muluk
    Nov 26 '15 at 11:12



















18














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






share|improve this answer





















  • 17





    -1, This is NOT joining data from two collections. It is using data from a single collection (actors) pivoting data around. So that things that were keys are now values and values are now keys... very different than a JOIN.

    – Evan Teran
    May 22 '12 at 17:44






  • 12





    This exactly what you have to do, MongoDB is not relational but document oriented. MapReduce allows to play with data with big performance (you can use cluster etc....) but even for simple cases, its very useful !

    – Thomas Decaux
    Jun 17 '12 at 19:16













  • Yet the best approach. Very useful thanks

    – Singh
    Oct 15 '15 at 13:45



















16














As others have pointed out you are trying to create a relational database from none relational database which you really don't want to do but anyways, if you have a case that you have to do this here is a solution you can use. We first do a foreach find on collection A( or in your case users) and then we get each item as an object then we use object property (in your case uid) to lookup in our second collection (in your case comments) if we can find it then we have a match and we can print or do something with it.
Hope this helps you and good luck :)



db.users.find().forEach(
function (object) {
var commonInBoth=db.comments.findOne({ "uid": object.uid} );
if (commonInBoth != null) {
printjson(commonInBoth) ;
printjson(object) ;
}else {
// did not match so we don't care in this case
}
});





share|improve this answer





















  • 1





    Don't see any reason for this to be downvoted.

    – Michael Cole
    Sep 21 '15 at 14:59











  • Wont this find the item we are currently looping on?

    – Skarlinski
    Mar 29 '17 at 12:58











  • This is poor performance wise for large collections.

    – Kusaasira Joshua
    Feb 7 at 10:08



















10














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/






share|improve this answer





















  • 7





    "Normalization comes from the concept of trying to save space" I question this. IMHO normalization comes from the concept of avoiding redundancy. Say you store the name of a user along with a blogpost. What if she marries? In a not normalized model you will have to wade through all posts and change the name. In a normalized model you usually change ONE record.

    – DanielKhan
    Nov 27 '13 at 13:28













  • @DanielKhan preventing redundancy and saving space are similar concepts, but on re-analysis I do agree, redundancy is the root cause for this design. I'll reword. Thanks for the note.

    – Snowburnt
    Nov 27 '13 at 19:15



















10














You can join two collection in Mongo by using lookup which is offered in 3.2 version. In your case the query would be



db.comments.aggregate({
$lookup:{
from:"users",
localField:"uid",
foreignField:"uid",
as:"users_comments"
}
})


or you can also join with respect to users then there will be a little change as given below.



db.users.aggregate({
$lookup:{
from:"comments",
localField:"uid",
foreignField:"uid",
as:"users_comments"
}
})


It will work just same as left and right join in SQL.






share|improve this answer

































    9














    There is a specification that a lot of drivers support that's called DBRef.




    DBRef is a more formal specification for creating references between documents. DBRefs (generally) include a collection name as well as an object id. Most developers only use DBRefs if the collection can change from one document to the next. If your referenced collection will always be the same, the manual references outlined above are more efficient.




    Taken from MongoDB Documentation: Data Models > Data Model Reference >
    Database References






    share|improve this answer

































      8














      With right combination of $lookup, $project and $match, you can join mutiple tables on multiple parameters. This is because they can be chained multiple times.



      Suppose we want to do following (reference)



      SELECT S.* FROM LeftTable S
      LEFT JOIN RightTable R ON S.ID =R.ID AND S.MID =R.MID WHERE R.TIM >0 AND
      S.MOB IS NOT NULL


      Step 1: Link all tables



      you can $lookup as many tables as you want.



      $lookup - one for each table in query



      $unwind - because data is denormalised correctly, else wrapped in arrays



      Python code..



      db.LeftTable.aggregate([
      # connect all tables

      {"$lookup": {
      "from": "RightTable",
      "localField": "ID",
      "foreignField": "ID",
      "as": "R"
      }},
      {"$unwind": "R"}

      ])


      Step 2: Define all conditionals



      $project : define all conditional statements here, plus all the variables you'd like to select.



      Python Code..



      db.LeftTable.aggregate([
      # connect all tables

      {"$lookup": {
      "from": "RightTable",
      "localField": "ID",
      "foreignField": "ID",
      "as": "R"
      }},
      {"$unwind": "R"},

      # define conditionals + variables

      {"$project": {
      "midEq": {"$eq": ["$MID", "$R.MID"]},
      "ID": 1, "MOB": 1, "MID": 1
      }}
      ])


      Step 3: Join all the conditionals



      $match - join all conditions using OR or AND etc. There can be multiples of these.



      $project: undefine all conditionals



      Python Code..



      db.LeftTable.aggregate([
      # connect all tables

      {"$lookup": {
      "from": "RightTable",
      "localField": "ID",
      "foreignField": "ID",
      "as": "R"
      }},
      {"$unwind": "$R"},

      # define conditionals + variables

      {"$project": {
      "midEq": {"$eq": ["$MID", "$R.MID"]},
      "ID": 1, "MOB": 1, "MID": 1
      }},

      # join all conditionals

      {"$match": {
      "$and": [
      {"R.TIM": {"$gt": 0}},
      {"MOB": {"$exists": True}},
      {"midEq": {"$eq": True}}
      ]}},

      # undefine conditionals

      {"$project": {
      "midEq": 0
      }}

      ])


      Pretty much any combination of tables, conditionals and joins can be done in this manner.






      share|improve this answer

































        5














        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"}},
        ])





        share|improve this answer































          4














          $lookup (aggregation)




          Performs a left outer join to an unsharded collection in the same database to filter in documents from the “joined” collection for processing. 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.
          The $lookup stage has the following syntaxes:



          Equality Match




          To perform an equality match between a field from the input documents with a field from the documents of the “joined” collection, the $lookup stage has the following syntax:



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


          The operation would correspond to the following pseudo-SQL statement:



          SELECT *, <output array field>
          FROM collection
          WHERE <output array field> IN (SELECT <documents as determined from the pipeline>
          FROM <collection to join>
          WHERE <pipeline> );


          Mongo URL






          share|improve this answer































            3














            You can run SQL queries including join on MongoDB with mongo_fdw from Postgres.






            share|improve this answer

































              2














              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;


              });

              });





              share|improve this answer


























              • Don't see any reason for this to be downvoted.

                – Michael Cole
                Sep 21 '15 at 14:59











              • Indeed I upvoted due to is a solution

                – Jnmgr
                Oct 29 '15 at 10:21



















              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.






              share|improve this answer































                0














                playORM can do it for you using S-SQL(Scalable SQL) which just adds partitioning such that you can do joins within partitions.






                share|improve this answer































                  -3














                  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.






                  share|improve this answer
























                  • 'you don't have to deal with duplicate rows for "many" sided joins' - no idea what you mean by this. Can you clarify?

                    – Mark Amery
                    Sep 20 '15 at 20:23













                  • Downvote, really?

                    – Michael Cole
                    Sep 21 '15 at 14:46






                  • 1





                    @MarkAmery, sure. In SQL a n-n relationship will return duplicate rows. E.g. Friends. If Bob is friends with Mary and Jane, you'll get 2 rows for Bob: Bob,Mary and Bob,Jane. 2 Bobs is a lie, there is only one Bob. With client-side joins you can start with Bob and decorate how you like: Bob, "Mary and Jane". SQL let's you do this with subqueries, but that's doing work on the db server that could be done on the client.

                    – Michael Cole
                    Sep 21 '15 at 14:51





















                  -3














                  I think, if You need normalized data tables - You need to try some other database solutions.



                  But I've foun that sollution for MOngo on Git
                  By the way, in inserts code - it has movie's name, but noi movie's ID.



                  Problem



                  You have a collection of Actors with an array of the Movies they've done.



                  You want to generate a collection of Movies with an array of Actors in each.



                  Some sample data



                   db.actors.insert( { actor: "Richard Gere", movies: ['Pretty Woman', 'Runaway Bride', 'Chicago'] });
                  db.actors.insert( { actor: "Julia Roberts", movies: ['Pretty Woman', 'Runaway Bride', 'Erin Brockovich'] });


                  Solution



                  We need to loop through each movie in the Actor document and emit each Movie individually.



                  The catch here is in the reduce phase. We cannot emit an array from the reduce phase, so we must build an Actors array inside of the "value" document that is returned.



                  The code

                  map = function() {
                  for(var i in this.movies){
                  key = { movie: this.movies[i] };
                  value = { actors: [ this.actor ] };
                  emit(key, value);
                  }
                  }

                  reduce = function(key, values) {
                  actor_list = { actors: };
                  for(var i in values) {
                  actor_list.actors = values[i].actors.concat(actor_list.actors);
                  }
                  return actor_list;
                  }


                  Notice how actor_list is actually a javascript object that contains an array. Also notice that map emits the same structure.



                  Run the following to execute the map / reduce, output it to the "pivot" collection and print the result:



                  printjson(db.actors.mapReduce(map, reduce, "pivot"));
                  db.pivot.find().forEach(printjson);



                  Here is the sample output, note that "Pretty Woman" and "Runaway Bride" have both "Richard Gere" and "Julia Roberts".



                  { "_id" : { "movie" : "Chicago" }, "value" : { "actors" : [ "Richard Gere" ] } }
                  { "_id" : { "movie" : "Erin Brockovich" }, "value" : { "actors" : [ "Julia Roberts" ] } }
                  { "_id" : { "movie" : "Pretty Woman" }, "value" : { "actors" : [ "Richard Gere", "Julia Roberts" ] } }
                  { "_id" : { "movie" : "Runaway Bride" }, "value" : { "actors" : [ "Richard Gere", "Julia Roberts" ] } }








                  share|improve this answer
























                  • Note that most of the content of this answer (i.e. the bit that's in comprehensible English) is copied from the MongoDB cookbook at the GitHub link the answerer provided.

                    – Mark Amery
                    Oct 5 '15 at 14:23



















                  -4














                  We can merge two collection by using mongoDB sub query. Here is example,
                  Commentss--



                  `db.commentss.insert([
                  { uid:12345, pid:444, comment:"blah" },
                  { uid:12345, pid:888, comment:"asdf" },
                  { uid:99999, pid:444, comment:"qwer" }])`


                  Userss--



                  db.userss.insert([
                  { uid:12345, name:"john" },
                  { uid:99999, name:"mia" }])


                  MongoDB sub query for JOIN--



                  `db.commentss.find().forEach(
                  function (newComments) {
                  newComments.userss = db.userss.find( { "uid": newComments.uid } ).toArray();
                  db.newCommentUsers.insert(newComments);
                  }
                  );`


                  Get result from newly generated Collection--



                  db.newCommentUsers.find().pretty()


                  Result--



                  `{
                  "_id" : ObjectId("5511236e29709afa03f226ef"),
                  "uid" : 12345,
                  "pid" : 444,
                  "comment" : "blah",
                  "userss" : [
                  {
                  "_id" : ObjectId("5511238129709afa03f226f2"),
                  "uid" : 12345,
                  "name" : "john"
                  }
                  ]
                  }
                  {
                  "_id" : ObjectId("5511236e29709afa03f226f0"),
                  "uid" : 12345,
                  "pid" : 888,
                  "comment" : "asdf",
                  "userss" : [
                  {
                  "_id" : ObjectId("5511238129709afa03f226f2"),
                  "uid" : 12345,
                  "name" : "john"
                  }
                  ]
                  }
                  {
                  "_id" : ObjectId("5511236e29709afa03f226f1"),
                  "uid" : 99999,
                  "pid" : 444,
                  "comment" : "qwer",
                  "userss" : [
                  {
                  "_id" : ObjectId("5511238129709afa03f226f3"),
                  "uid" : 99999,
                  "name" : "mia"
                  }
                  ]
                  }`


                  Hope so this will help.






                  share|improve this answer



















                  • 7





                    Why did you basically copy this nearly identical, one-year-old answer? stackoverflow.com/a/22739813/4186945

                    – hackel
                    May 5 '15 at 20:45










                  protected by Community Jun 29 '15 at 9:46



                  Thank you for your interest in this question.
                  Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).



                  Would you like to answer one of these unanswered questions instead?














                  19 Answers
                  19






                  active

                  oldest

                  votes








                  19 Answers
                  19






                  active

                  oldest

                  votes









                  active

                  oldest

                  votes






                  active

                  oldest

                  votes









                  257














                  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.






                  share|improve this answer





















                  • 11





                    is $lookup performance-wise the better option?

                    – Mohsen Shakiba
                    Nov 21 '15 at 9:06






                  • 5





                    Agreed. This is the best answer.

                    – NDB
                    Nov 23 '15 at 13:21











                  • @clayton : How about more then two collections?

                    – Dipen Dedania
                    Jan 8 '16 at 10:36






                  • 1





                    @DipenDedania just add additional $lookup stages to the aggregation pipeline.

                    – Clayton Gulick
                    Jan 9 '16 at 1:01











                  • I cant join any field in array in left collection with its corresponding id in right collection.can anybody help me??

                    – Prateek Singh
                    Feb 1 '16 at 16:52
















                  257














                  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.






                  share|improve this answer





















                  • 11





                    is $lookup performance-wise the better option?

                    – Mohsen Shakiba
                    Nov 21 '15 at 9:06






                  • 5





                    Agreed. This is the best answer.

                    – NDB
                    Nov 23 '15 at 13:21











                  • @clayton : How about more then two collections?

                    – Dipen Dedania
                    Jan 8 '16 at 10:36






                  • 1





                    @DipenDedania just add additional $lookup stages to the aggregation pipeline.

                    – Clayton Gulick
                    Jan 9 '16 at 1:01











                  • I cant join any field in array in left collection with its corresponding id in right collection.can anybody help me??

                    – Prateek Singh
                    Feb 1 '16 at 16:52














                  257












                  257








                  257







                  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.






                  share|improve this answer















                  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.







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Apr 10 '17 at 5:25









                  Ahmad Baktash Hayeri

                  4,67222237




                  4,67222237










                  answered Nov 3 '15 at 23:35









                  Clayton GulickClayton Gulick

                  6,74722721




                  6,74722721








                  • 11





                    is $lookup performance-wise the better option?

                    – Mohsen Shakiba
                    Nov 21 '15 at 9:06






                  • 5





                    Agreed. This is the best answer.

                    – NDB
                    Nov 23 '15 at 13:21











                  • @clayton : How about more then two collections?

                    – Dipen Dedania
                    Jan 8 '16 at 10:36






                  • 1





                    @DipenDedania just add additional $lookup stages to the aggregation pipeline.

                    – Clayton Gulick
                    Jan 9 '16 at 1:01











                  • I cant join any field in array in left collection with its corresponding id in right collection.can anybody help me??

                    – Prateek Singh
                    Feb 1 '16 at 16:52














                  • 11





                    is $lookup performance-wise the better option?

                    – Mohsen Shakiba
                    Nov 21 '15 at 9:06






                  • 5





                    Agreed. This is the best answer.

                    – NDB
                    Nov 23 '15 at 13:21











                  • @clayton : How about more then two collections?

                    – Dipen Dedania
                    Jan 8 '16 at 10:36






                  • 1





                    @DipenDedania just add additional $lookup stages to the aggregation pipeline.

                    – Clayton Gulick
                    Jan 9 '16 at 1:01











                  • I cant join any field in array in left collection with its corresponding id in right collection.can anybody help me??

                    – Prateek Singh
                    Feb 1 '16 at 16:52








                  11




                  11





                  is $lookup performance-wise the better option?

                  – Mohsen Shakiba
                  Nov 21 '15 at 9:06





                  is $lookup performance-wise the better option?

                  – Mohsen Shakiba
                  Nov 21 '15 at 9:06




                  5




                  5





                  Agreed. This is the best answer.

                  – NDB
                  Nov 23 '15 at 13:21





                  Agreed. This is the best answer.

                  – NDB
                  Nov 23 '15 at 13:21













                  @clayton : How about more then two collections?

                  – Dipen Dedania
                  Jan 8 '16 at 10:36





                  @clayton : How about more then two collections?

                  – Dipen Dedania
                  Jan 8 '16 at 10:36




                  1




                  1





                  @DipenDedania just add additional $lookup stages to the aggregation pipeline.

                  – Clayton Gulick
                  Jan 9 '16 at 1:01





                  @DipenDedania just add additional $lookup stages to the aggregation pipeline.

                  – Clayton Gulick
                  Jan 9 '16 at 1:01













                  I cant join any field in array in left collection with its corresponding id in right collection.can anybody help me??

                  – Prateek Singh
                  Feb 1 '16 at 16:52





                  I cant join any field in array in left collection with its corresponding id in right collection.can anybody help me??

                  – Prateek Singh
                  Feb 1 '16 at 16:52













                  136














                  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.







                  share|improve this answer





















                  • 114





                    Then why so many people love MongoDB. I dont get it :|

                    – Boris D. Teoharov
                    Sep 15 '13 at 15:47






                  • 48





                    @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.

                    – Snowburnt
                    Nov 4 '13 at 1:53






                  • 17





                    @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.

                    – Snowburnt
                    Nov 12 '13 at 0:30






                  • 9





                    Migrations and a constantly evolving schemas are also a lot easier to manage on a NoSQL system.

                    – justin
                    May 6 '14 at 20:09






                  • 11





                    What if the user has 3.540 posts in the website, and he does change his username in profile? Should every post be updated with the new username?

                    – Ivo Pereira
                    Mar 2 '16 at 17:39
















                  136














                  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.







                  share|improve this answer





















                  • 114





                    Then why so many people love MongoDB. I dont get it :|

                    – Boris D. Teoharov
                    Sep 15 '13 at 15:47






                  • 48





                    @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.

                    – Snowburnt
                    Nov 4 '13 at 1:53






                  • 17





                    @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.

                    – Snowburnt
                    Nov 12 '13 at 0:30






                  • 9





                    Migrations and a constantly evolving schemas are also a lot easier to manage on a NoSQL system.

                    – justin
                    May 6 '14 at 20:09






                  • 11





                    What if the user has 3.540 posts in the website, and he does change his username in profile? Should every post be updated with the new username?

                    – Ivo Pereira
                    Mar 2 '16 at 17:39














                  136












                  136








                  136







                  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.







                  share|improve this answer















                  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.








                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Jul 13 '15 at 17:11









                  KyleMit

                  59.4k37248412




                  59.4k37248412










                  answered Feb 8 '11 at 20:04









                  William SteinWilliam Stein

                  2,0231139




                  2,0231139








                  • 114





                    Then why so many people love MongoDB. I dont get it :|

                    – Boris D. Teoharov
                    Sep 15 '13 at 15:47






                  • 48





                    @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.

                    – Snowburnt
                    Nov 4 '13 at 1:53






                  • 17





                    @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.

                    – Snowburnt
                    Nov 12 '13 at 0:30






                  • 9





                    Migrations and a constantly evolving schemas are also a lot easier to manage on a NoSQL system.

                    – justin
                    May 6 '14 at 20:09






                  • 11





                    What if the user has 3.540 posts in the website, and he does change his username in profile? Should every post be updated with the new username?

                    – Ivo Pereira
                    Mar 2 '16 at 17:39














                  • 114





                    Then why so many people love MongoDB. I dont get it :|

                    – Boris D. Teoharov
                    Sep 15 '13 at 15:47






                  • 48





                    @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.

                    – Snowburnt
                    Nov 4 '13 at 1:53






                  • 17





                    @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.

                    – Snowburnt
                    Nov 12 '13 at 0:30






                  • 9





                    Migrations and a constantly evolving schemas are also a lot easier to manage on a NoSQL system.

                    – justin
                    May 6 '14 at 20:09






                  • 11





                    What if the user has 3.540 posts in the website, and he does change his username in profile? Should every post be updated with the new username?

                    – Ivo Pereira
                    Mar 2 '16 at 17:39








                  114




                  114





                  Then why so many people love MongoDB. I dont get it :|

                  – Boris D. Teoharov
                  Sep 15 '13 at 15:47





                  Then why so many people love MongoDB. I dont get it :|

                  – Boris D. Teoharov
                  Sep 15 '13 at 15:47




                  48




                  48





                  @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.

                  – Snowburnt
                  Nov 4 '13 at 1:53





                  @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.

                  – Snowburnt
                  Nov 4 '13 at 1:53




                  17




                  17





                  @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.

                  – Snowburnt
                  Nov 12 '13 at 0:30





                  @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.

                  – Snowburnt
                  Nov 12 '13 at 0:30




                  9




                  9





                  Migrations and a constantly evolving schemas are also a lot easier to manage on a NoSQL system.

                  – justin
                  May 6 '14 at 20:09





                  Migrations and a constantly evolving schemas are also a lot easier to manage on a NoSQL system.

                  – justin
                  May 6 '14 at 20:09




                  11




                  11





                  What if the user has 3.540 posts in the website, and he does change his username in profile? Should every post be updated with the new username?

                  – Ivo Pereira
                  Mar 2 '16 at 17:39





                  What if the user has 3.540 posts in the website, and he does change his username in profile? Should every post be updated with the new username?

                  – Ivo Pereira
                  Mar 2 '16 at 17:39











                  130














                  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.






                  share|improve this answer



















                  • 2





                    i'm wondering if this same code can be ran using doctrine mongodb?

                    – abbood
                    May 30 '14 at 13:46






                  • 4





                    What happens when one of the references objects gets an update? Does that update automatically reflect in the book object? Or does that loop need to run again?

                    – balupton
                    Jun 4 '14 at 5:55






                  • 9





                    This is fine as long as your data is small. It is going to bring each book content to your client and then fetch each category, lending and authors one by one. The moment your books are in thousands, this would go really really slow. A better technique probably would be to use aggregation pipeline and output the merged data into a separate collection. Let me get back to it again. I will add that an answer.

                    – Sandeep Giri
                    Jun 19 '14 at 15:31













                  • Can you adapt your algorithm to this other example? stackoverflow.com/q/32718079/287948

                    – Peter Krauss
                    Sep 22 '15 at 14:13






                  • 1





                    @SandeepGiri how can i do the aggregate pipeline since i have really really intensive data in separated collection need join ??

                    – Yassine Abdul-Rahman
                    Oct 7 '15 at 20:16
















                  130














                  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.






                  share|improve this answer



















                  • 2





                    i'm wondering if this same code can be ran using doctrine mongodb?

                    – abbood
                    May 30 '14 at 13:46






                  • 4





                    What happens when one of the references objects gets an update? Does that update automatically reflect in the book object? Or does that loop need to run again?

                    – balupton
                    Jun 4 '14 at 5:55






                  • 9





                    This is fine as long as your data is small. It is going to bring each book content to your client and then fetch each category, lending and authors one by one. The moment your books are in thousands, this would go really really slow. A better technique probably would be to use aggregation pipeline and output the merged data into a separate collection. Let me get back to it again. I will add that an answer.

                    – Sandeep Giri
                    Jun 19 '14 at 15:31













                  • Can you adapt your algorithm to this other example? stackoverflow.com/q/32718079/287948

                    – Peter Krauss
                    Sep 22 '15 at 14:13






                  • 1





                    @SandeepGiri how can i do the aggregate pipeline since i have really really intensive data in separated collection need join ??

                    – Yassine Abdul-Rahman
                    Oct 7 '15 at 20:16














                  130












                  130








                  130







                  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.






                  share|improve this answer













                  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.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Mar 30 '14 at 3:12









                  Orlando BecerraOrlando Becerra

                  1,309163




                  1,309163








                  • 2





                    i'm wondering if this same code can be ran using doctrine mongodb?

                    – abbood
                    May 30 '14 at 13:46






                  • 4





                    What happens when one of the references objects gets an update? Does that update automatically reflect in the book object? Or does that loop need to run again?

                    – balupton
                    Jun 4 '14 at 5:55






                  • 9





                    This is fine as long as your data is small. It is going to bring each book content to your client and then fetch each category, lending and authors one by one. The moment your books are in thousands, this would go really really slow. A better technique probably would be to use aggregation pipeline and output the merged data into a separate collection. Let me get back to it again. I will add that an answer.

                    – Sandeep Giri
                    Jun 19 '14 at 15:31













                  • Can you adapt your algorithm to this other example? stackoverflow.com/q/32718079/287948

                    – Peter Krauss
                    Sep 22 '15 at 14:13






                  • 1





                    @SandeepGiri how can i do the aggregate pipeline since i have really really intensive data in separated collection need join ??

                    – Yassine Abdul-Rahman
                    Oct 7 '15 at 20:16














                  • 2





                    i'm wondering if this same code can be ran using doctrine mongodb?

                    – abbood
                    May 30 '14 at 13:46






                  • 4





                    What happens when one of the references objects gets an update? Does that update automatically reflect in the book object? Or does that loop need to run again?

                    – balupton
                    Jun 4 '14 at 5:55






                  • 9





                    This is fine as long as your data is small. It is going to bring each book content to your client and then fetch each category, lending and authors one by one. The moment your books are in thousands, this would go really really slow. A better technique probably would be to use aggregation pipeline and output the merged data into a separate collection. Let me get back to it again. I will add that an answer.

                    – Sandeep Giri
                    Jun 19 '14 at 15:31













                  • Can you adapt your algorithm to this other example? stackoverflow.com/q/32718079/287948

                    – Peter Krauss
                    Sep 22 '15 at 14:13






                  • 1





                    @SandeepGiri how can i do the aggregate pipeline since i have really really intensive data in separated collection need join ??

                    – Yassine Abdul-Rahman
                    Oct 7 '15 at 20:16








                  2




                  2





                  i'm wondering if this same code can be ran using doctrine mongodb?

                  – abbood
                  May 30 '14 at 13:46





                  i'm wondering if this same code can be ran using doctrine mongodb?

                  – abbood
                  May 30 '14 at 13:46




                  4




                  4





                  What happens when one of the references objects gets an update? Does that update automatically reflect in the book object? Or does that loop need to run again?

                  – balupton
                  Jun 4 '14 at 5:55





                  What happens when one of the references objects gets an update? Does that update automatically reflect in the book object? Or does that loop need to run again?

                  – balupton
                  Jun 4 '14 at 5:55




                  9




                  9





                  This is fine as long as your data is small. It is going to bring each book content to your client and then fetch each category, lending and authors one by one. The moment your books are in thousands, this would go really really slow. A better technique probably would be to use aggregation pipeline and output the merged data into a separate collection. Let me get back to it again. I will add that an answer.

                  – Sandeep Giri
                  Jun 19 '14 at 15:31







                  This is fine as long as your data is small. It is going to bring each book content to your client and then fetch each category, lending and authors one by one. The moment your books are in thousands, this would go really really slow. A better technique probably would be to use aggregation pipeline and output the merged data into a separate collection. Let me get back to it again. I will add that an answer.

                  – Sandeep Giri
                  Jun 19 '14 at 15:31















                  Can you adapt your algorithm to this other example? stackoverflow.com/q/32718079/287948

                  – Peter Krauss
                  Sep 22 '15 at 14:13





                  Can you adapt your algorithm to this other example? stackoverflow.com/q/32718079/287948

                  – Peter Krauss
                  Sep 22 '15 at 14:13




                  1




                  1





                  @SandeepGiri how can i do the aggregate pipeline since i have really really intensive data in separated collection need join ??

                  – Yassine Abdul-Rahman
                  Oct 7 '15 at 20:16





                  @SandeepGiri how can i do the aggregate pipeline since i have really really intensive data in separated collection need join ??

                  – Yassine Abdul-Rahman
                  Oct 7 '15 at 20:16











                  37














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






                  share|improve this answer



















                  • 4





                    Seems wrong performance wise coming from a sql server background, but its maybe not that bad with a document db?

                    – terjetyl
                    Jul 15 '10 at 18:20






                  • 3





                    from a sql server background as well, I would appreciate MongoDB taking a 'result set' (with selected returned fields) as input for a new query in one go, much like nested queries in SQL

                    – Stijn Sanders
                    Nov 26 '10 at 23:17






                  • 1





                    @terjetyl You have to really plan for it. What fields are you going be presenting on the front end, if it's a limited amount in an individual view then you take those as embedded documents. The key is to not need to do joins. If you want to do deep analysis, you do it after the fact in another database. Run a job that transforms the data into an OLAP cube for optimal performance.

                    – Snowburnt
                    Nov 4 '13 at 1:56






                  • 3





                    From mongo 3.2 version left joins are supported.

                    – Somnath Muluk
                    Nov 26 '15 at 11:12
















                  37














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






                  share|improve this answer



















                  • 4





                    Seems wrong performance wise coming from a sql server background, but its maybe not that bad with a document db?

                    – terjetyl
                    Jul 15 '10 at 18:20






                  • 3





                    from a sql server background as well, I would appreciate MongoDB taking a 'result set' (with selected returned fields) as input for a new query in one go, much like nested queries in SQL

                    – Stijn Sanders
                    Nov 26 '10 at 23:17






                  • 1





                    @terjetyl You have to really plan for it. What fields are you going be presenting on the front end, if it's a limited amount in an individual view then you take those as embedded documents. The key is to not need to do joins. If you want to do deep analysis, you do it after the fact in another database. Run a job that transforms the data into an OLAP cube for optimal performance.

                    – Snowburnt
                    Nov 4 '13 at 1:56






                  • 3





                    From mongo 3.2 version left joins are supported.

                    – Somnath Muluk
                    Nov 26 '15 at 11:12














                  37












                  37








                  37







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






                  share|improve this answer













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







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Feb 28 '10 at 11:34









                  Otto AllmendingerOtto Allmendinger

                  20.6k45474




                  20.6k45474








                  • 4





                    Seems wrong performance wise coming from a sql server background, but its maybe not that bad with a document db?

                    – terjetyl
                    Jul 15 '10 at 18:20






                  • 3





                    from a sql server background as well, I would appreciate MongoDB taking a 'result set' (with selected returned fields) as input for a new query in one go, much like nested queries in SQL

                    – Stijn Sanders
                    Nov 26 '10 at 23:17






                  • 1





                    @terjetyl You have to really plan for it. What fields are you going be presenting on the front end, if it's a limited amount in an individual view then you take those as embedded documents. The key is to not need to do joins. If you want to do deep analysis, you do it after the fact in another database. Run a job that transforms the data into an OLAP cube for optimal performance.

                    – Snowburnt
                    Nov 4 '13 at 1:56






                  • 3





                    From mongo 3.2 version left joins are supported.

                    – Somnath Muluk
                    Nov 26 '15 at 11:12














                  • 4





                    Seems wrong performance wise coming from a sql server background, but its maybe not that bad with a document db?

                    – terjetyl
                    Jul 15 '10 at 18:20






                  • 3





                    from a sql server background as well, I would appreciate MongoDB taking a 'result set' (with selected returned fields) as input for a new query in one go, much like nested queries in SQL

                    – Stijn Sanders
                    Nov 26 '10 at 23:17






                  • 1





                    @terjetyl You have to really plan for it. What fields are you going be presenting on the front end, if it's a limited amount in an individual view then you take those as embedded documents. The key is to not need to do joins. If you want to do deep analysis, you do it after the fact in another database. Run a job that transforms the data into an OLAP cube for optimal performance.

                    – Snowburnt
                    Nov 4 '13 at 1:56






                  • 3





                    From mongo 3.2 version left joins are supported.

                    – Somnath Muluk
                    Nov 26 '15 at 11:12








                  4




                  4





                  Seems wrong performance wise coming from a sql server background, but its maybe not that bad with a document db?

                  – terjetyl
                  Jul 15 '10 at 18:20





                  Seems wrong performance wise coming from a sql server background, but its maybe not that bad with a document db?

                  – terjetyl
                  Jul 15 '10 at 18:20




                  3




                  3





                  from a sql server background as well, I would appreciate MongoDB taking a 'result set' (with selected returned fields) as input for a new query in one go, much like nested queries in SQL

                  – Stijn Sanders
                  Nov 26 '10 at 23:17





                  from a sql server background as well, I would appreciate MongoDB taking a 'result set' (with selected returned fields) as input for a new query in one go, much like nested queries in SQL

                  – Stijn Sanders
                  Nov 26 '10 at 23:17




                  1




                  1





                  @terjetyl You have to really plan for it. What fields are you going be presenting on the front end, if it's a limited amount in an individual view then you take those as embedded documents. The key is to not need to do joins. If you want to do deep analysis, you do it after the fact in another database. Run a job that transforms the data into an OLAP cube for optimal performance.

                  – Snowburnt
                  Nov 4 '13 at 1:56





                  @terjetyl You have to really plan for it. What fields are you going be presenting on the front end, if it's a limited amount in an individual view then you take those as embedded documents. The key is to not need to do joins. If you want to do deep analysis, you do it after the fact in another database. Run a job that transforms the data into an OLAP cube for optimal performance.

                  – Snowburnt
                  Nov 4 '13 at 1:56




                  3




                  3





                  From mongo 3.2 version left joins are supported.

                  – Somnath Muluk
                  Nov 26 '15 at 11:12





                  From mongo 3.2 version left joins are supported.

                  – Somnath Muluk
                  Nov 26 '15 at 11:12











                  18














                  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






                  share|improve this answer





















                  • 17





                    -1, This is NOT joining data from two collections. It is using data from a single collection (actors) pivoting data around. So that things that were keys are now values and values are now keys... very different than a JOIN.

                    – Evan Teran
                    May 22 '12 at 17:44






                  • 12





                    This exactly what you have to do, MongoDB is not relational but document oriented. MapReduce allows to play with data with big performance (you can use cluster etc....) but even for simple cases, its very useful !

                    – Thomas Decaux
                    Jun 17 '12 at 19:16













                  • Yet the best approach. Very useful thanks

                    – Singh
                    Oct 15 '15 at 13:45
















                  18














                  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






                  share|improve this answer





















                  • 17





                    -1, This is NOT joining data from two collections. It is using data from a single collection (actors) pivoting data around. So that things that were keys are now values and values are now keys... very different than a JOIN.

                    – Evan Teran
                    May 22 '12 at 17:44






                  • 12





                    This exactly what you have to do, MongoDB is not relational but document oriented. MapReduce allows to play with data with big performance (you can use cluster etc....) but even for simple cases, its very useful !

                    – Thomas Decaux
                    Jun 17 '12 at 19:16













                  • Yet the best approach. Very useful thanks

                    – Singh
                    Oct 15 '15 at 13:45














                  18












                  18








                  18







                  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






                  share|improve this answer















                  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







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Aug 28 '15 at 16:42









                  JosephSlote

                  261210




                  261210










                  answered Mar 25 '12 at 10:54









                  antitoxicantitoxic

                  3,0522943




                  3,0522943








                  • 17





                    -1, This is NOT joining data from two collections. It is using data from a single collection (actors) pivoting data around. So that things that were keys are now values and values are now keys... very different than a JOIN.

                    – Evan Teran
                    May 22 '12 at 17:44






                  • 12





                    This exactly what you have to do, MongoDB is not relational but document oriented. MapReduce allows to play with data with big performance (you can use cluster etc....) but even for simple cases, its very useful !

                    – Thomas Decaux
                    Jun 17 '12 at 19:16













                  • Yet the best approach. Very useful thanks

                    – Singh
                    Oct 15 '15 at 13:45














                  • 17





                    -1, This is NOT joining data from two collections. It is using data from a single collection (actors) pivoting data around. So that things that were keys are now values and values are now keys... very different than a JOIN.

                    – Evan Teran
                    May 22 '12 at 17:44






                  • 12





                    This exactly what you have to do, MongoDB is not relational but document oriented. MapReduce allows to play with data with big performance (you can use cluster etc....) but even for simple cases, its very useful !

                    – Thomas Decaux
                    Jun 17 '12 at 19:16













                  • Yet the best approach. Very useful thanks

                    – Singh
                    Oct 15 '15 at 13:45








                  17




                  17





                  -1, This is NOT joining data from two collections. It is using data from a single collection (actors) pivoting data around. So that things that were keys are now values and values are now keys... very different than a JOIN.

                  – Evan Teran
                  May 22 '12 at 17:44





                  -1, This is NOT joining data from two collections. It is using data from a single collection (actors) pivoting data around. So that things that were keys are now values and values are now keys... very different than a JOIN.

                  – Evan Teran
                  May 22 '12 at 17:44




                  12




                  12





                  This exactly what you have to do, MongoDB is not relational but document oriented. MapReduce allows to play with data with big performance (you can use cluster etc....) but even for simple cases, its very useful !

                  – Thomas Decaux
                  Jun 17 '12 at 19:16







                  This exactly what you have to do, MongoDB is not relational but document oriented. MapReduce allows to play with data with big performance (you can use cluster etc....) but even for simple cases, its very useful !

                  – Thomas Decaux
                  Jun 17 '12 at 19:16















                  Yet the best approach. Very useful thanks

                  – Singh
                  Oct 15 '15 at 13:45





                  Yet the best approach. Very useful thanks

                  – Singh
                  Oct 15 '15 at 13:45











                  16














                  As others have pointed out you are trying to create a relational database from none relational database which you really don't want to do but anyways, if you have a case that you have to do this here is a solution you can use. We first do a foreach find on collection A( or in your case users) and then we get each item as an object then we use object property (in your case uid) to lookup in our second collection (in your case comments) if we can find it then we have a match and we can print or do something with it.
                  Hope this helps you and good luck :)



                  db.users.find().forEach(
                  function (object) {
                  var commonInBoth=db.comments.findOne({ "uid": object.uid} );
                  if (commonInBoth != null) {
                  printjson(commonInBoth) ;
                  printjson(object) ;
                  }else {
                  // did not match so we don't care in this case
                  }
                  });





                  share|improve this answer





















                  • 1





                    Don't see any reason for this to be downvoted.

                    – Michael Cole
                    Sep 21 '15 at 14:59











                  • Wont this find the item we are currently looping on?

                    – Skarlinski
                    Mar 29 '17 at 12:58











                  • This is poor performance wise for large collections.

                    – Kusaasira Joshua
                    Feb 7 at 10:08
















                  16














                  As others have pointed out you are trying to create a relational database from none relational database which you really don't want to do but anyways, if you have a case that you have to do this here is a solution you can use. We first do a foreach find on collection A( or in your case users) and then we get each item as an object then we use object property (in your case uid) to lookup in our second collection (in your case comments) if we can find it then we have a match and we can print or do something with it.
                  Hope this helps you and good luck :)



                  db.users.find().forEach(
                  function (object) {
                  var commonInBoth=db.comments.findOne({ "uid": object.uid} );
                  if (commonInBoth != null) {
                  printjson(commonInBoth) ;
                  printjson(object) ;
                  }else {
                  // did not match so we don't care in this case
                  }
                  });





                  share|improve this answer





















                  • 1





                    Don't see any reason for this to be downvoted.

                    – Michael Cole
                    Sep 21 '15 at 14:59











                  • Wont this find the item we are currently looping on?

                    – Skarlinski
                    Mar 29 '17 at 12:58











                  • This is poor performance wise for large collections.

                    – Kusaasira Joshua
                    Feb 7 at 10:08














                  16












                  16








                  16







                  As others have pointed out you are trying to create a relational database from none relational database which you really don't want to do but anyways, if you have a case that you have to do this here is a solution you can use. We first do a foreach find on collection A( or in your case users) and then we get each item as an object then we use object property (in your case uid) to lookup in our second collection (in your case comments) if we can find it then we have a match and we can print or do something with it.
                  Hope this helps you and good luck :)



                  db.users.find().forEach(
                  function (object) {
                  var commonInBoth=db.comments.findOne({ "uid": object.uid} );
                  if (commonInBoth != null) {
                  printjson(commonInBoth) ;
                  printjson(object) ;
                  }else {
                  // did not match so we don't care in this case
                  }
                  });





                  share|improve this answer















                  As others have pointed out you are trying to create a relational database from none relational database which you really don't want to do but anyways, if you have a case that you have to do this here is a solution you can use. We first do a foreach find on collection A( or in your case users) and then we get each item as an object then we use object property (in your case uid) to lookup in our second collection (in your case comments) if we can find it then we have a match and we can print or do something with it.
                  Hope this helps you and good luck :)



                  db.users.find().forEach(
                  function (object) {
                  var commonInBoth=db.comments.findOne({ "uid": object.uid} );
                  if (commonInBoth != null) {
                  printjson(commonInBoth) ;
                  printjson(object) ;
                  }else {
                  // did not match so we don't care in this case
                  }
                  });






                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Feb 17 '16 at 13:06









                  VishAl

                  166116




                  166116










                  answered May 4 '15 at 5:49









                  grepitgrepit

                  9,72525854




                  9,72525854








                  • 1





                    Don't see any reason for this to be downvoted.

                    – Michael Cole
                    Sep 21 '15 at 14:59











                  • Wont this find the item we are currently looping on?

                    – Skarlinski
                    Mar 29 '17 at 12:58











                  • This is poor performance wise for large collections.

                    – Kusaasira Joshua
                    Feb 7 at 10:08














                  • 1





                    Don't see any reason for this to be downvoted.

                    – Michael Cole
                    Sep 21 '15 at 14:59











                  • Wont this find the item we are currently looping on?

                    – Skarlinski
                    Mar 29 '17 at 12:58











                  • This is poor performance wise for large collections.

                    – Kusaasira Joshua
                    Feb 7 at 10:08








                  1




                  1





                  Don't see any reason for this to be downvoted.

                  – Michael Cole
                  Sep 21 '15 at 14:59





                  Don't see any reason for this to be downvoted.

                  – Michael Cole
                  Sep 21 '15 at 14:59













                  Wont this find the item we are currently looping on?

                  – Skarlinski
                  Mar 29 '17 at 12:58





                  Wont this find the item we are currently looping on?

                  – Skarlinski
                  Mar 29 '17 at 12:58













                  This is poor performance wise for large collections.

                  – Kusaasira Joshua
                  Feb 7 at 10:08





                  This is poor performance wise for large collections.

                  – Kusaasira Joshua
                  Feb 7 at 10:08











                  10














                  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/






                  share|improve this answer





















                  • 7





                    "Normalization comes from the concept of trying to save space" I question this. IMHO normalization comes from the concept of avoiding redundancy. Say you store the name of a user along with a blogpost. What if she marries? In a not normalized model you will have to wade through all posts and change the name. In a normalized model you usually change ONE record.

                    – DanielKhan
                    Nov 27 '13 at 13:28













                  • @DanielKhan preventing redundancy and saving space are similar concepts, but on re-analysis I do agree, redundancy is the root cause for this design. I'll reword. Thanks for the note.

                    – Snowburnt
                    Nov 27 '13 at 19:15
















                  10














                  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/






                  share|improve this answer





















                  • 7





                    "Normalization comes from the concept of trying to save space" I question this. IMHO normalization comes from the concept of avoiding redundancy. Say you store the name of a user along with a blogpost. What if she marries? In a not normalized model you will have to wade through all posts and change the name. In a normalized model you usually change ONE record.

                    – DanielKhan
                    Nov 27 '13 at 13:28













                  • @DanielKhan preventing redundancy and saving space are similar concepts, but on re-analysis I do agree, redundancy is the root cause for this design. I'll reword. Thanks for the note.

                    – Snowburnt
                    Nov 27 '13 at 19:15














                  10












                  10








                  10







                  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/






                  share|improve this answer















                  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/







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Nov 27 '13 at 19:18

























                  answered Nov 5 '13 at 14:11









                  SnowburntSnowburnt

                  3,19342341




                  3,19342341








                  • 7





                    "Normalization comes from the concept of trying to save space" I question this. IMHO normalization comes from the concept of avoiding redundancy. Say you store the name of a user along with a blogpost. What if she marries? In a not normalized model you will have to wade through all posts and change the name. In a normalized model you usually change ONE record.

                    – DanielKhan
                    Nov 27 '13 at 13:28













                  • @DanielKhan preventing redundancy and saving space are similar concepts, but on re-analysis I do agree, redundancy is the root cause for this design. I'll reword. Thanks for the note.

                    – Snowburnt
                    Nov 27 '13 at 19:15














                  • 7





                    "Normalization comes from the concept of trying to save space" I question this. IMHO normalization comes from the concept of avoiding redundancy. Say you store the name of a user along with a blogpost. What if she marries? In a not normalized model you will have to wade through all posts and change the name. In a normalized model you usually change ONE record.

                    – DanielKhan
                    Nov 27 '13 at 13:28













                  • @DanielKhan preventing redundancy and saving space are similar concepts, but on re-analysis I do agree, redundancy is the root cause for this design. I'll reword. Thanks for the note.

                    – Snowburnt
                    Nov 27 '13 at 19:15








                  7




                  7





                  "Normalization comes from the concept of trying to save space" I question this. IMHO normalization comes from the concept of avoiding redundancy. Say you store the name of a user along with a blogpost. What if she marries? In a not normalized model you will have to wade through all posts and change the name. In a normalized model you usually change ONE record.

                  – DanielKhan
                  Nov 27 '13 at 13:28







                  "Normalization comes from the concept of trying to save space" I question this. IMHO normalization comes from the concept of avoiding redundancy. Say you store the name of a user along with a blogpost. What if she marries? In a not normalized model you will have to wade through all posts and change the name. In a normalized model you usually change ONE record.

                  – DanielKhan
                  Nov 27 '13 at 13:28















                  @DanielKhan preventing redundancy and saving space are similar concepts, but on re-analysis I do agree, redundancy is the root cause for this design. I'll reword. Thanks for the note.

                  – Snowburnt
                  Nov 27 '13 at 19:15





                  @DanielKhan preventing redundancy and saving space are similar concepts, but on re-analysis I do agree, redundancy is the root cause for this design. I'll reword. Thanks for the note.

                  – Snowburnt
                  Nov 27 '13 at 19:15











                  10














                  You can join two collection in Mongo by using lookup which is offered in 3.2 version. In your case the query would be



                  db.comments.aggregate({
                  $lookup:{
                  from:"users",
                  localField:"uid",
                  foreignField:"uid",
                  as:"users_comments"
                  }
                  })


                  or you can also join with respect to users then there will be a little change as given below.



                  db.users.aggregate({
                  $lookup:{
                  from:"comments",
                  localField:"uid",
                  foreignField:"uid",
                  as:"users_comments"
                  }
                  })


                  It will work just same as left and right join in SQL.






                  share|improve this answer






























                    10














                    You can join two collection in Mongo by using lookup which is offered in 3.2 version. In your case the query would be



                    db.comments.aggregate({
                    $lookup:{
                    from:"users",
                    localField:"uid",
                    foreignField:"uid",
                    as:"users_comments"
                    }
                    })


                    or you can also join with respect to users then there will be a little change as given below.



                    db.users.aggregate({
                    $lookup:{
                    from:"comments",
                    localField:"uid",
                    foreignField:"uid",
                    as:"users_comments"
                    }
                    })


                    It will work just same as left and right join in SQL.






                    share|improve this answer




























                      10












                      10








                      10







                      You can join two collection in Mongo by using lookup which is offered in 3.2 version. In your case the query would be



                      db.comments.aggregate({
                      $lookup:{
                      from:"users",
                      localField:"uid",
                      foreignField:"uid",
                      as:"users_comments"
                      }
                      })


                      or you can also join with respect to users then there will be a little change as given below.



                      db.users.aggregate({
                      $lookup:{
                      from:"comments",
                      localField:"uid",
                      foreignField:"uid",
                      as:"users_comments"
                      }
                      })


                      It will work just same as left and right join in SQL.






                      share|improve this answer















                      You can join two collection in Mongo by using lookup which is offered in 3.2 version. In your case the query would be



                      db.comments.aggregate({
                      $lookup:{
                      from:"users",
                      localField:"uid",
                      foreignField:"uid",
                      as:"users_comments"
                      }
                      })


                      or you can also join with respect to users then there will be a little change as given below.



                      db.users.aggregate({
                      $lookup:{
                      from:"comments",
                      localField:"uid",
                      foreignField:"uid",
                      as:"users_comments"
                      }
                      })


                      It will work just same as left and right join in SQL.







                      share|improve this answer














                      share|improve this answer



                      share|improve this answer








                      edited Aug 9 '16 at 13:35









                      Alex M

                      2,38072029




                      2,38072029










                      answered Aug 9 '16 at 12:29









                      jarry jaferyjarry jafery

                      505718




                      505718























                          9














                          There is a specification that a lot of drivers support that's called DBRef.




                          DBRef is a more formal specification for creating references between documents. DBRefs (generally) include a collection name as well as an object id. Most developers only use DBRefs if the collection can change from one document to the next. If your referenced collection will always be the same, the manual references outlined above are more efficient.




                          Taken from MongoDB Documentation: Data Models > Data Model Reference >
                          Database References






                          share|improve this answer






























                            9














                            There is a specification that a lot of drivers support that's called DBRef.




                            DBRef is a more formal specification for creating references between documents. DBRefs (generally) include a collection name as well as an object id. Most developers only use DBRefs if the collection can change from one document to the next. If your referenced collection will always be the same, the manual references outlined above are more efficient.




                            Taken from MongoDB Documentation: Data Models > Data Model Reference >
                            Database References






                            share|improve this answer




























                              9












                              9








                              9







                              There is a specification that a lot of drivers support that's called DBRef.




                              DBRef is a more formal specification for creating references between documents. DBRefs (generally) include a collection name as well as an object id. Most developers only use DBRefs if the collection can change from one document to the next. If your referenced collection will always be the same, the manual references outlined above are more efficient.




                              Taken from MongoDB Documentation: Data Models > Data Model Reference >
                              Database References






                              share|improve this answer















                              There is a specification that a lot of drivers support that's called DBRef.




                              DBRef is a more formal specification for creating references between documents. DBRefs (generally) include a collection name as well as an object id. Most developers only use DBRefs if the collection can change from one document to the next. If your referenced collection will always be the same, the manual references outlined above are more efficient.




                              Taken from MongoDB Documentation: Data Models > Data Model Reference >
                              Database References







                              share|improve this answer














                              share|improve this answer



                              share|improve this answer








                              edited Nov 23 '15 at 13:13









                              NDB

                              503516




                              503516










                              answered Mar 1 '10 at 18:27









                              PickelsPickels

                              21.4k20101165




                              21.4k20101165























                                  8














                                  With right combination of $lookup, $project and $match, you can join mutiple tables on multiple parameters. This is because they can be chained multiple times.



                                  Suppose we want to do following (reference)



                                  SELECT S.* FROM LeftTable S
                                  LEFT JOIN RightTable R ON S.ID =R.ID AND S.MID =R.MID WHERE R.TIM >0 AND
                                  S.MOB IS NOT NULL


                                  Step 1: Link all tables



                                  you can $lookup as many tables as you want.



                                  $lookup - one for each table in query



                                  $unwind - because data is denormalised correctly, else wrapped in arrays



                                  Python code..



                                  db.LeftTable.aggregate([
                                  # connect all tables

                                  {"$lookup": {
                                  "from": "RightTable",
                                  "localField": "ID",
                                  "foreignField": "ID",
                                  "as": "R"
                                  }},
                                  {"$unwind": "R"}

                                  ])


                                  Step 2: Define all conditionals



                                  $project : define all conditional statements here, plus all the variables you'd like to select.



                                  Python Code..



                                  db.LeftTable.aggregate([
                                  # connect all tables

                                  {"$lookup": {
                                  "from": "RightTable",
                                  "localField": "ID",
                                  "foreignField": "ID",
                                  "as": "R"
                                  }},
                                  {"$unwind": "R"},

                                  # define conditionals + variables

                                  {"$project": {
                                  "midEq": {"$eq": ["$MID", "$R.MID"]},
                                  "ID": 1, "MOB": 1, "MID": 1
                                  }}
                                  ])


                                  Step 3: Join all the conditionals



                                  $match - join all conditions using OR or AND etc. There can be multiples of these.



                                  $project: undefine all conditionals



                                  Python Code..



                                  db.LeftTable.aggregate([
                                  # connect all tables

                                  {"$lookup": {
                                  "from": "RightTable",
                                  "localField": "ID",
                                  "foreignField": "ID",
                                  "as": "R"
                                  }},
                                  {"$unwind": "$R"},

                                  # define conditionals + variables

                                  {"$project": {
                                  "midEq": {"$eq": ["$MID", "$R.MID"]},
                                  "ID": 1, "MOB": 1, "MID": 1
                                  }},

                                  # join all conditionals

                                  {"$match": {
                                  "$and": [
                                  {"R.TIM": {"$gt": 0}},
                                  {"MOB": {"$exists": True}},
                                  {"midEq": {"$eq": True}}
                                  ]}},

                                  # undefine conditionals

                                  {"$project": {
                                  "midEq": 0
                                  }}

                                  ])


                                  Pretty much any combination of tables, conditionals and joins can be done in this manner.






                                  share|improve this answer






























                                    8














                                    With right combination of $lookup, $project and $match, you can join mutiple tables on multiple parameters. This is because they can be chained multiple times.



                                    Suppose we want to do following (reference)



                                    SELECT S.* FROM LeftTable S
                                    LEFT JOIN RightTable R ON S.ID =R.ID AND S.MID =R.MID WHERE R.TIM >0 AND
                                    S.MOB IS NOT NULL


                                    Step 1: Link all tables



                                    you can $lookup as many tables as you want.



                                    $lookup - one for each table in query



                                    $unwind - because data is denormalised correctly, else wrapped in arrays



                                    Python code..



                                    db.LeftTable.aggregate([
                                    # connect all tables

                                    {"$lookup": {
                                    "from": "RightTable",
                                    "localField": "ID",
                                    "foreignField": "ID",
                                    "as": "R"
                                    }},
                                    {"$unwind": "R"}

                                    ])


                                    Step 2: Define all conditionals



                                    $project : define all conditional statements here, plus all the variables you'd like to select.



                                    Python Code..



                                    db.LeftTable.aggregate([
                                    # connect all tables

                                    {"$lookup": {
                                    "from": "RightTable",
                                    "localField": "ID",
                                    "foreignField": "ID",
                                    "as": "R"
                                    }},
                                    {"$unwind": "R"},

                                    # define conditionals + variables

                                    {"$project": {
                                    "midEq": {"$eq": ["$MID", "$R.MID"]},
                                    "ID": 1, "MOB": 1, "MID": 1
                                    }}
                                    ])


                                    Step 3: Join all the conditionals



                                    $match - join all conditions using OR or AND etc. There can be multiples of these.



                                    $project: undefine all conditionals



                                    Python Code..



                                    db.LeftTable.aggregate([
                                    # connect all tables

                                    {"$lookup": {
                                    "from": "RightTable",
                                    "localField": "ID",
                                    "foreignField": "ID",
                                    "as": "R"
                                    }},
                                    {"$unwind": "$R"},

                                    # define conditionals + variables

                                    {"$project": {
                                    "midEq": {"$eq": ["$MID", "$R.MID"]},
                                    "ID": 1, "MOB": 1, "MID": 1
                                    }},

                                    # join all conditionals

                                    {"$match": {
                                    "$and": [
                                    {"R.TIM": {"$gt": 0}},
                                    {"MOB": {"$exists": True}},
                                    {"midEq": {"$eq": True}}
                                    ]}},

                                    # undefine conditionals

                                    {"$project": {
                                    "midEq": 0
                                    }}

                                    ])


                                    Pretty much any combination of tables, conditionals and joins can be done in this manner.






                                    share|improve this answer




























                                      8












                                      8








                                      8







                                      With right combination of $lookup, $project and $match, you can join mutiple tables on multiple parameters. This is because they can be chained multiple times.



                                      Suppose we want to do following (reference)



                                      SELECT S.* FROM LeftTable S
                                      LEFT JOIN RightTable R ON S.ID =R.ID AND S.MID =R.MID WHERE R.TIM >0 AND
                                      S.MOB IS NOT NULL


                                      Step 1: Link all tables



                                      you can $lookup as many tables as you want.



                                      $lookup - one for each table in query



                                      $unwind - because data is denormalised correctly, else wrapped in arrays



                                      Python code..



                                      db.LeftTable.aggregate([
                                      # connect all tables

                                      {"$lookup": {
                                      "from": "RightTable",
                                      "localField": "ID",
                                      "foreignField": "ID",
                                      "as": "R"
                                      }},
                                      {"$unwind": "R"}

                                      ])


                                      Step 2: Define all conditionals



                                      $project : define all conditional statements here, plus all the variables you'd like to select.



                                      Python Code..



                                      db.LeftTable.aggregate([
                                      # connect all tables

                                      {"$lookup": {
                                      "from": "RightTable",
                                      "localField": "ID",
                                      "foreignField": "ID",
                                      "as": "R"
                                      }},
                                      {"$unwind": "R"},

                                      # define conditionals + variables

                                      {"$project": {
                                      "midEq": {"$eq": ["$MID", "$R.MID"]},
                                      "ID": 1, "MOB": 1, "MID": 1
                                      }}
                                      ])


                                      Step 3: Join all the conditionals



                                      $match - join all conditions using OR or AND etc. There can be multiples of these.



                                      $project: undefine all conditionals



                                      Python Code..



                                      db.LeftTable.aggregate([
                                      # connect all tables

                                      {"$lookup": {
                                      "from": "RightTable",
                                      "localField": "ID",
                                      "foreignField": "ID",
                                      "as": "R"
                                      }},
                                      {"$unwind": "$R"},

                                      # define conditionals + variables

                                      {"$project": {
                                      "midEq": {"$eq": ["$MID", "$R.MID"]},
                                      "ID": 1, "MOB": 1, "MID": 1
                                      }},

                                      # join all conditionals

                                      {"$match": {
                                      "$and": [
                                      {"R.TIM": {"$gt": 0}},
                                      {"MOB": {"$exists": True}},
                                      {"midEq": {"$eq": True}}
                                      ]}},

                                      # undefine conditionals

                                      {"$project": {
                                      "midEq": 0
                                      }}

                                      ])


                                      Pretty much any combination of tables, conditionals and joins can be done in this manner.






                                      share|improve this answer















                                      With right combination of $lookup, $project and $match, you can join mutiple tables on multiple parameters. This is because they can be chained multiple times.



                                      Suppose we want to do following (reference)



                                      SELECT S.* FROM LeftTable S
                                      LEFT JOIN RightTable R ON S.ID =R.ID AND S.MID =R.MID WHERE R.TIM >0 AND
                                      S.MOB IS NOT NULL


                                      Step 1: Link all tables



                                      you can $lookup as many tables as you want.



                                      $lookup - one for each table in query



                                      $unwind - because data is denormalised correctly, else wrapped in arrays



                                      Python code..



                                      db.LeftTable.aggregate([
                                      # connect all tables

                                      {"$lookup": {
                                      "from": "RightTable",
                                      "localField": "ID",
                                      "foreignField": "ID",
                                      "as": "R"
                                      }},
                                      {"$unwind": "R"}

                                      ])


                                      Step 2: Define all conditionals



                                      $project : define all conditional statements here, plus all the variables you'd like to select.



                                      Python Code..



                                      db.LeftTable.aggregate([
                                      # connect all tables

                                      {"$lookup": {
                                      "from": "RightTable",
                                      "localField": "ID",
                                      "foreignField": "ID",
                                      "as": "R"
                                      }},
                                      {"$unwind": "R"},

                                      # define conditionals + variables

                                      {"$project": {
                                      "midEq": {"$eq": ["$MID", "$R.MID"]},
                                      "ID": 1, "MOB": 1, "MID": 1
                                      }}
                                      ])


                                      Step 3: Join all the conditionals



                                      $match - join all conditions using OR or AND etc. There can be multiples of these.



                                      $project: undefine all conditionals



                                      Python Code..



                                      db.LeftTable.aggregate([
                                      # connect all tables

                                      {"$lookup": {
                                      "from": "RightTable",
                                      "localField": "ID",
                                      "foreignField": "ID",
                                      "as": "R"
                                      }},
                                      {"$unwind": "$R"},

                                      # define conditionals + variables

                                      {"$project": {
                                      "midEq": {"$eq": ["$MID", "$R.MID"]},
                                      "ID": 1, "MOB": 1, "MID": 1
                                      }},

                                      # join all conditionals

                                      {"$match": {
                                      "$and": [
                                      {"R.TIM": {"$gt": 0}},
                                      {"MOB": {"$exists": True}},
                                      {"midEq": {"$eq": True}}
                                      ]}},

                                      # undefine conditionals

                                      {"$project": {
                                      "midEq": 0
                                      }}

                                      ])


                                      Pretty much any combination of tables, conditionals and joins can be done in this manner.







                                      share|improve this answer














                                      share|improve this answer



                                      share|improve this answer








                                      edited May 23 '17 at 12:18









                                      Community

                                      11




                                      11










                                      answered Apr 19 '17 at 3:28









                                      Shaurabh BhartiShaurabh Bharti

                                      492513




                                      492513























                                          5














                                          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"}},
                                          ])





                                          share|improve this answer




























                                            5














                                            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"}},
                                            ])





                                            share|improve this answer


























                                              5












                                              5








                                              5







                                              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"}},
                                              ])





                                              share|improve this answer













                                              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"}},
                                              ])






                                              share|improve this answer












                                              share|improve this answer



                                              share|improve this answer










                                              answered Oct 22 '16 at 4:19









                                              Anish AgarwalAnish Agarwal

                                              1,2691415




                                              1,2691415























                                                  4














                                                  $lookup (aggregation)




                                                  Performs a left outer join to an unsharded collection in the same database to filter in documents from the “joined” collection for processing. 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.
                                                  The $lookup stage has the following syntaxes:



                                                  Equality Match




                                                  To perform an equality match between a field from the input documents with a field from the documents of the “joined” collection, the $lookup stage has the following syntax:



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


                                                  The operation would correspond to the following pseudo-SQL statement:



                                                  SELECT *, <output array field>
                                                  FROM collection
                                                  WHERE <output array field> IN (SELECT <documents as determined from the pipeline>
                                                  FROM <collection to join>
                                                  WHERE <pipeline> );


                                                  Mongo URL






                                                  share|improve this answer




























                                                    4














                                                    $lookup (aggregation)




                                                    Performs a left outer join to an unsharded collection in the same database to filter in documents from the “joined” collection for processing. 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.
                                                    The $lookup stage has the following syntaxes:



                                                    Equality Match




                                                    To perform an equality match between a field from the input documents with a field from the documents of the “joined” collection, the $lookup stage has the following syntax:



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


                                                    The operation would correspond to the following pseudo-SQL statement:



                                                    SELECT *, <output array field>
                                                    FROM collection
                                                    WHERE <output array field> IN (SELECT <documents as determined from the pipeline>
                                                    FROM <collection to join>
                                                    WHERE <pipeline> );


                                                    Mongo URL






                                                    share|improve this answer


























                                                      4












                                                      4








                                                      4







                                                      $lookup (aggregation)




                                                      Performs a left outer join to an unsharded collection in the same database to filter in documents from the “joined” collection for processing. 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.
                                                      The $lookup stage has the following syntaxes:



                                                      Equality Match




                                                      To perform an equality match between a field from the input documents with a field from the documents of the “joined” collection, the $lookup stage has the following syntax:



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


                                                      The operation would correspond to the following pseudo-SQL statement:



                                                      SELECT *, <output array field>
                                                      FROM collection
                                                      WHERE <output array field> IN (SELECT <documents as determined from the pipeline>
                                                      FROM <collection to join>
                                                      WHERE <pipeline> );


                                                      Mongo URL






                                                      share|improve this answer













                                                      $lookup (aggregation)




                                                      Performs a left outer join to an unsharded collection in the same database to filter in documents from the “joined” collection for processing. 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.
                                                      The $lookup stage has the following syntaxes:



                                                      Equality Match




                                                      To perform an equality match between a field from the input documents with a field from the documents of the “joined” collection, the $lookup stage has the following syntax:



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


                                                      The operation would correspond to the following pseudo-SQL statement:



                                                      SELECT *, <output array field>
                                                      FROM collection
                                                      WHERE <output array field> IN (SELECT <documents as determined from the pipeline>
                                                      FROM <collection to join>
                                                      WHERE <pipeline> );


                                                      Mongo URL







                                                      share|improve this answer












                                                      share|improve this answer



                                                      share|improve this answer










                                                      answered Mar 27 '18 at 12:19









                                                      GoutamSGoutamS

                                                      1,4661020




                                                      1,4661020























                                                          3














                                                          You can run SQL queries including join on MongoDB with mongo_fdw from Postgres.






                                                          share|improve this answer






























                                                            3














                                                            You can run SQL queries including join on MongoDB with mongo_fdw from Postgres.






                                                            share|improve this answer




























                                                              3












                                                              3








                                                              3







                                                              You can run SQL queries including join on MongoDB with mongo_fdw from Postgres.






                                                              share|improve this answer















                                                              You can run SQL queries including join on MongoDB with mongo_fdw from Postgres.







                                                              share|improve this answer














                                                              share|improve this answer



                                                              share|improve this answer








                                                              edited Jun 14 '13 at 15:21

























                                                              answered Oct 18 '12 at 7:02









                                                              metdosmetdos

                                                              5,512105597




                                                              5,512105597























                                                                  2














                                                                  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;


                                                                  });

                                                                  });





                                                                  share|improve this answer


























                                                                  • Don't see any reason for this to be downvoted.

                                                                    – Michael Cole
                                                                    Sep 21 '15 at 14:59











                                                                  • Indeed I upvoted due to is a solution

                                                                    – Jnmgr
                                                                    Oct 29 '15 at 10:21
















                                                                  2














                                                                  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;


                                                                  });

                                                                  });





                                                                  share|improve this answer


























                                                                  • Don't see any reason for this to be downvoted.

                                                                    – Michael Cole
                                                                    Sep 21 '15 at 14:59











                                                                  • Indeed I upvoted due to is a solution

                                                                    – Jnmgr
                                                                    Oct 29 '15 at 10:21














                                                                  2












                                                                  2








                                                                  2







                                                                  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;


                                                                  });

                                                                  });





                                                                  share|improve this answer















                                                                  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;


                                                                  });

                                                                  });






                                                                  share|improve this answer














                                                                  share|improve this answer



                                                                  share|improve this answer








                                                                  edited Oct 15 '15 at 21:15









                                                                  Michael Mior

                                                                  21.7k66293




                                                                  21.7k66293










                                                                  answered Apr 14 '15 at 15:33









                                                                  Amine_DevAmine_Dev

                                                                  462411




                                                                  462411













                                                                  • Don't see any reason for this to be downvoted.

                                                                    – Michael Cole
                                                                    Sep 21 '15 at 14:59











                                                                  • Indeed I upvoted due to is a solution

                                                                    – Jnmgr
                                                                    Oct 29 '15 at 10:21



















                                                                  • Don't see any reason for this to be downvoted.

                                                                    – Michael Cole
                                                                    Sep 21 '15 at 14:59











                                                                  • Indeed I upvoted due to is a solution

                                                                    – Jnmgr
                                                                    Oct 29 '15 at 10:21

















                                                                  Don't see any reason for this to be downvoted.

                                                                  – Michael Cole
                                                                  Sep 21 '15 at 14:59





                                                                  Don't see any reason for this to be downvoted.

                                                                  – Michael Cole
                                                                  Sep 21 '15 at 14:59













                                                                  Indeed I upvoted due to is a solution

                                                                  – Jnmgr
                                                                  Oct 29 '15 at 10:21





                                                                  Indeed I upvoted due to is a solution

                                                                  – Jnmgr
                                                                  Oct 29 '15 at 10:21











                                                                  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.






                                                                  share|improve this 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.






                                                                    share|improve this answer


























                                                                      1












                                                                      1








                                                                      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.






                                                                      share|improve this answer













                                                                      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.







                                                                      share|improve this answer












                                                                      share|improve this answer



                                                                      share|improve this answer










                                                                      answered Dec 13 '17 at 14:28









                                                                      Marcelo LazaroniMarcelo Lazaroni

                                                                      3,98112124




                                                                      3,98112124























                                                                          0














                                                                          playORM can do it for you using S-SQL(Scalable SQL) which just adds partitioning such that you can do joins within partitions.






                                                                          share|improve this answer




























                                                                            0














                                                                            playORM can do it for you using S-SQL(Scalable SQL) which just adds partitioning such that you can do joins within partitions.






                                                                            share|improve this answer


























                                                                              0












                                                                              0








                                                                              0







                                                                              playORM can do it for you using S-SQL(Scalable SQL) which just adds partitioning such that you can do joins within partitions.






                                                                              share|improve this answer













                                                                              playORM can do it for you using S-SQL(Scalable SQL) which just adds partitioning such that you can do joins within partitions.







                                                                              share|improve this answer












                                                                              share|improve this answer



                                                                              share|improve this answer










                                                                              answered Sep 7 '12 at 17:30









                                                                              Dean HillerDean Hiller

                                                                              10.5k1586150




                                                                              10.5k1586150























                                                                                  -3














                                                                                  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.






                                                                                  share|improve this answer
























                                                                                  • 'you don't have to deal with duplicate rows for "many" sided joins' - no idea what you mean by this. Can you clarify?

                                                                                    – Mark Amery
                                                                                    Sep 20 '15 at 20:23













                                                                                  • Downvote, really?

                                                                                    – Michael Cole
                                                                                    Sep 21 '15 at 14:46






                                                                                  • 1





                                                                                    @MarkAmery, sure. In SQL a n-n relationship will return duplicate rows. E.g. Friends. If Bob is friends with Mary and Jane, you'll get 2 rows for Bob: Bob,Mary and Bob,Jane. 2 Bobs is a lie, there is only one Bob. With client-side joins you can start with Bob and decorate how you like: Bob, "Mary and Jane". SQL let's you do this with subqueries, but that's doing work on the db server that could be done on the client.

                                                                                    – Michael Cole
                                                                                    Sep 21 '15 at 14:51


















                                                                                  -3














                                                                                  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.






                                                                                  share|improve this answer
























                                                                                  • 'you don't have to deal with duplicate rows for "many" sided joins' - no idea what you mean by this. Can you clarify?

                                                                                    – Mark Amery
                                                                                    Sep 20 '15 at 20:23













                                                                                  • Downvote, really?

                                                                                    – Michael Cole
                                                                                    Sep 21 '15 at 14:46






                                                                                  • 1





                                                                                    @MarkAmery, sure. In SQL a n-n relationship will return duplicate rows. E.g. Friends. If Bob is friends with Mary and Jane, you'll get 2 rows for Bob: Bob,Mary and Bob,Jane. 2 Bobs is a lie, there is only one Bob. With client-side joins you can start with Bob and decorate how you like: Bob, "Mary and Jane". SQL let's you do this with subqueries, but that's doing work on the db server that could be done on the client.

                                                                                    – Michael Cole
                                                                                    Sep 21 '15 at 14:51
















                                                                                  -3












                                                                                  -3








                                                                                  -3







                                                                                  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.






                                                                                  share|improve this answer













                                                                                  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.







                                                                                  share|improve this answer












                                                                                  share|improve this answer



                                                                                  share|improve this answer










                                                                                  answered Sep 4 '15 at 15:23









                                                                                  Michael ColeMichael Cole

                                                                                  9,54534256




                                                                                  9,54534256













                                                                                  • 'you don't have to deal with duplicate rows for "many" sided joins' - no idea what you mean by this. Can you clarify?

                                                                                    – Mark Amery
                                                                                    Sep 20 '15 at 20:23













                                                                                  • Downvote, really?

                                                                                    – Michael Cole
                                                                                    Sep 21 '15 at 14:46






                                                                                  • 1





                                                                                    @MarkAmery, sure. In SQL a n-n relationship will return duplicate rows. E.g. Friends. If Bob is friends with Mary and Jane, you'll get 2 rows for Bob: Bob,Mary and Bob,Jane. 2 Bobs is a lie, there is only one Bob. With client-side joins you can start with Bob and decorate how you like: Bob, "Mary and Jane". SQL let's you do this with subqueries, but that's doing work on the db server that could be done on the client.

                                                                                    – Michael Cole
                                                                                    Sep 21 '15 at 14:51





















                                                                                  • 'you don't have to deal with duplicate rows for "many" sided joins' - no idea what you mean by this. Can you clarify?

                                                                                    – Mark Amery
                                                                                    Sep 20 '15 at 20:23













                                                                                  • Downvote, really?

                                                                                    – Michael Cole
                                                                                    Sep 21 '15 at 14:46






                                                                                  • 1





                                                                                    @MarkAmery, sure. In SQL a n-n relationship will return duplicate rows. E.g. Friends. If Bob is friends with Mary and Jane, you'll get 2 rows for Bob: Bob,Mary and Bob,Jane. 2 Bobs is a lie, there is only one Bob. With client-side joins you can start with Bob and decorate how you like: Bob, "Mary and Jane". SQL let's you do this with subqueries, but that's doing work on the db server that could be done on the client.

                                                                                    – Michael Cole
                                                                                    Sep 21 '15 at 14:51



















                                                                                  'you don't have to deal with duplicate rows for "many" sided joins' - no idea what you mean by this. Can you clarify?

                                                                                  – Mark Amery
                                                                                  Sep 20 '15 at 20:23







                                                                                  'you don't have to deal with duplicate rows for "many" sided joins' - no idea what you mean by this. Can you clarify?

                                                                                  – Mark Amery
                                                                                  Sep 20 '15 at 20:23















                                                                                  Downvote, really?

                                                                                  – Michael Cole
                                                                                  Sep 21 '15 at 14:46





                                                                                  Downvote, really?

                                                                                  – Michael Cole
                                                                                  Sep 21 '15 at 14:46




                                                                                  1




                                                                                  1





                                                                                  @MarkAmery, sure. In SQL a n-n relationship will return duplicate rows. E.g. Friends. If Bob is friends with Mary and Jane, you'll get 2 rows for Bob: Bob,Mary and Bob,Jane. 2 Bobs is a lie, there is only one Bob. With client-side joins you can start with Bob and decorate how you like: Bob, "Mary and Jane". SQL let's you do this with subqueries, but that's doing work on the db server that could be done on the client.

                                                                                  – Michael Cole
                                                                                  Sep 21 '15 at 14:51







                                                                                  @MarkAmery, sure. In SQL a n-n relationship will return duplicate rows. E.g. Friends. If Bob is friends with Mary and Jane, you'll get 2 rows for Bob: Bob,Mary and Bob,Jane. 2 Bobs is a lie, there is only one Bob. With client-side joins you can start with Bob and decorate how you like: Bob, "Mary and Jane". SQL let's you do this with subqueries, but that's doing work on the db server that could be done on the client.

                                                                                  – Michael Cole
                                                                                  Sep 21 '15 at 14:51













                                                                                  -3














                                                                                  I think, if You need normalized data tables - You need to try some other database solutions.



                                                                                  But I've foun that sollution for MOngo on Git
                                                                                  By the way, in inserts code - it has movie's name, but noi movie's ID.



                                                                                  Problem



                                                                                  You have a collection of Actors with an array of the Movies they've done.



                                                                                  You want to generate a collection of Movies with an array of Actors in each.



                                                                                  Some sample data



                                                                                   db.actors.insert( { actor: "Richard Gere", movies: ['Pretty Woman', 'Runaway Bride', 'Chicago'] });
                                                                                  db.actors.insert( { actor: "Julia Roberts", movies: ['Pretty Woman', 'Runaway Bride', 'Erin Brockovich'] });


                                                                                  Solution



                                                                                  We need to loop through each movie in the Actor document and emit each Movie individually.



                                                                                  The catch here is in the reduce phase. We cannot emit an array from the reduce phase, so we must build an Actors array inside of the "value" document that is returned.



                                                                                  The code

                                                                                  map = function() {
                                                                                  for(var i in this.movies){
                                                                                  key = { movie: this.movies[i] };
                                                                                  value = { actors: [ this.actor ] };
                                                                                  emit(key, value);
                                                                                  }
                                                                                  }

                                                                                  reduce = function(key, values) {
                                                                                  actor_list = { actors: };
                                                                                  for(var i in values) {
                                                                                  actor_list.actors = values[i].actors.concat(actor_list.actors);
                                                                                  }
                                                                                  return actor_list;
                                                                                  }


                                                                                  Notice how actor_list is actually a javascript object that contains an array. Also notice that map emits the same structure.



                                                                                  Run the following to execute the map / reduce, output it to the "pivot" collection and print the result:



                                                                                  printjson(db.actors.mapReduce(map, reduce, "pivot"));
                                                                                  db.pivot.find().forEach(printjson);



                                                                                  Here is the sample output, note that "Pretty Woman" and "Runaway Bride" have both "Richard Gere" and "Julia Roberts".



                                                                                  { "_id" : { "movie" : "Chicago" }, "value" : { "actors" : [ "Richard Gere" ] } }
                                                                                  { "_id" : { "movie" : "Erin Brockovich" }, "value" : { "actors" : [ "Julia Roberts" ] } }
                                                                                  { "_id" : { "movie" : "Pretty Woman" }, "value" : { "actors" : [ "Richard Gere", "Julia Roberts" ] } }
                                                                                  { "_id" : { "movie" : "Runaway Bride" }, "value" : { "actors" : [ "Richard Gere", "Julia Roberts" ] } }








                                                                                  share|improve this answer
























                                                                                  • Note that most of the content of this answer (i.e. the bit that's in comprehensible English) is copied from the MongoDB cookbook at the GitHub link the answerer provided.

                                                                                    – Mark Amery
                                                                                    Oct 5 '15 at 14:23
















                                                                                  -3














                                                                                  I think, if You need normalized data tables - You need to try some other database solutions.



                                                                                  But I've foun that sollution for MOngo on Git
                                                                                  By the way, in inserts code - it has movie's name, but noi movie's ID.



                                                                                  Problem



                                                                                  You have a collection of Actors with an array of the Movies they've done.



                                                                                  You want to generate a collection of Movies with an array of Actors in each.



                                                                                  Some sample data



                                                                                   db.actors.insert( { actor: "Richard Gere", movies: ['Pretty Woman', 'Runaway Bride', 'Chicago'] });
                                                                                  db.actors.insert( { actor: "Julia Roberts", movies: ['Pretty Woman', 'Runaway Bride', 'Erin Brockovich'] });


                                                                                  Solution



                                                                                  We need to loop through each movie in the Actor document and emit each Movie individually.



                                                                                  The catch here is in the reduce phase. We cannot emit an array from the reduce phase, so we must build an Actors array inside of the "value" document that is returned.



                                                                                  The code

                                                                                  map = function() {
                                                                                  for(var i in this.movies){
                                                                                  key = { movie: this.movies[i] };
                                                                                  value = { actors: [ this.actor ] };
                                                                                  emit(key, value);
                                                                                  }
                                                                                  }

                                                                                  reduce = function(key, values) {
                                                                                  actor_list = { actors: };
                                                                                  for(var i in values) {
                                                                                  actor_list.actors = values[i].actors.concat(actor_list.actors);
                                                                                  }
                                                                                  return actor_list;
                                                                                  }


                                                                                  Notice how actor_list is actually a javascript object that contains an array. Also notice that map emits the same structure.



                                                                                  Run the following to execute the map / reduce, output it to the "pivot" collection and print the result:



                                                                                  printjson(db.actors.mapReduce(map, reduce, "pivot"));
                                                                                  db.pivot.find().forEach(printjson);



                                                                                  Here is the sample output, note that "Pretty Woman" and "Runaway Bride" have both "Richard Gere" and "Julia Roberts".



                                                                                  { "_id" : { "movie" : "Chicago" }, "value" : { "actors" : [ "Richard Gere" ] } }
                                                                                  { "_id" : { "movie" : "Erin Brockovich" }, "value" : { "actors" : [ "Julia Roberts" ] } }
                                                                                  { "_id" : { "movie" : "Pretty Woman" }, "value" : { "actors" : [ "Richard Gere", "Julia Roberts" ] } }
                                                                                  { "_id" : { "movie" : "Runaway Bride" }, "value" : { "actors" : [ "Richard Gere", "Julia Roberts" ] } }








                                                                                  share|improve this answer
























                                                                                  • Note that most of the content of this answer (i.e. the bit that's in comprehensible English) is copied from the MongoDB cookbook at the GitHub link the answerer provided.

                                                                                    – Mark Amery
                                                                                    Oct 5 '15 at 14:23














                                                                                  -3












                                                                                  -3








                                                                                  -3







                                                                                  I think, if You need normalized data tables - You need to try some other database solutions.



                                                                                  But I've foun that sollution for MOngo on Git
                                                                                  By the way, in inserts code - it has movie's name, but noi movie's ID.



                                                                                  Problem



                                                                                  You have a collection of Actors with an array of the Movies they've done.



                                                                                  You want to generate a collection of Movies with an array of Actors in each.



                                                                                  Some sample data



                                                                                   db.actors.insert( { actor: "Richard Gere", movies: ['Pretty Woman', 'Runaway Bride', 'Chicago'] });
                                                                                  db.actors.insert( { actor: "Julia Roberts", movies: ['Pretty Woman', 'Runaway Bride', 'Erin Brockovich'] });


                                                                                  Solution



                                                                                  We need to loop through each movie in the Actor document and emit each Movie individually.



                                                                                  The catch here is in the reduce phase. We cannot emit an array from the reduce phase, so we must build an Actors array inside of the "value" document that is returned.



                                                                                  The code

                                                                                  map = function() {
                                                                                  for(var i in this.movies){
                                                                                  key = { movie: this.movies[i] };
                                                                                  value = { actors: [ this.actor ] };
                                                                                  emit(key, value);
                                                                                  }
                                                                                  }

                                                                                  reduce = function(key, values) {
                                                                                  actor_list = { actors: };
                                                                                  for(var i in values) {
                                                                                  actor_list.actors = values[i].actors.concat(actor_list.actors);
                                                                                  }
                                                                                  return actor_list;
                                                                                  }


                                                                                  Notice how actor_list is actually a javascript object that contains an array. Also notice that map emits the same structure.



                                                                                  Run the following to execute the map / reduce, output it to the "pivot" collection and print the result:



                                                                                  printjson(db.actors.mapReduce(map, reduce, "pivot"));
                                                                                  db.pivot.find().forEach(printjson);



                                                                                  Here is the sample output, note that "Pretty Woman" and "Runaway Bride" have both "Richard Gere" and "Julia Roberts".



                                                                                  { "_id" : { "movie" : "Chicago" }, "value" : { "actors" : [ "Richard Gere" ] } }
                                                                                  { "_id" : { "movie" : "Erin Brockovich" }, "value" : { "actors" : [ "Julia Roberts" ] } }
                                                                                  { "_id" : { "movie" : "Pretty Woman" }, "value" : { "actors" : [ "Richard Gere", "Julia Roberts" ] } }
                                                                                  { "_id" : { "movie" : "Runaway Bride" }, "value" : { "actors" : [ "Richard Gere", "Julia Roberts" ] } }








                                                                                  share|improve this answer













                                                                                  I think, if You need normalized data tables - You need to try some other database solutions.



                                                                                  But I've foun that sollution for MOngo on Git
                                                                                  By the way, in inserts code - it has movie's name, but noi movie's ID.



                                                                                  Problem



                                                                                  You have a collection of Actors with an array of the Movies they've done.



                                                                                  You want to generate a collection of Movies with an array of Actors in each.



                                                                                  Some sample data



                                                                                   db.actors.insert( { actor: "Richard Gere", movies: ['Pretty Woman', 'Runaway Bride', 'Chicago'] });
                                                                                  db.actors.insert( { actor: "Julia Roberts", movies: ['Pretty Woman', 'Runaway Bride', 'Erin Brockovich'] });


                                                                                  Solution



                                                                                  We need to loop through each movie in the Actor document and emit each Movie individually.



                                                                                  The catch here is in the reduce phase. We cannot emit an array from the reduce phase, so we must build an Actors array inside of the "value" document that is returned.



                                                                                  The code

                                                                                  map = function() {
                                                                                  for(var i in this.movies){
                                                                                  key = { movie: this.movies[i] };
                                                                                  value = { actors: [ this.actor ] };
                                                                                  emit(key, value);
                                                                                  }
                                                                                  }

                                                                                  reduce = function(key, values) {
                                                                                  actor_list = { actors: };
                                                                                  for(var i in values) {
                                                                                  actor_list.actors = values[i].actors.concat(actor_list.actors);
                                                                                  }
                                                                                  return actor_list;
                                                                                  }


                                                                                  Notice how actor_list is actually a javascript object that contains an array. Also notice that map emits the same structure.



                                                                                  Run the following to execute the map / reduce, output it to the "pivot" collection and print the result:



                                                                                  printjson(db.actors.mapReduce(map, reduce, "pivot"));
                                                                                  db.pivot.find().forEach(printjson);



                                                                                  Here is the sample output, note that "Pretty Woman" and "Runaway Bride" have both "Richard Gere" and "Julia Roberts".



                                                                                  { "_id" : { "movie" : "Chicago" }, "value" : { "actors" : [ "Richard Gere" ] } }
                                                                                  { "_id" : { "movie" : "Erin Brockovich" }, "value" : { "actors" : [ "Julia Roberts" ] } }
                                                                                  { "_id" : { "movie" : "Pretty Woman" }, "value" : { "actors" : [ "Richard Gere", "Julia Roberts" ] } }
                                                                                  { "_id" : { "movie" : "Runaway Bride" }, "value" : { "actors" : [ "Richard Gere", "Julia Roberts" ] } }









                                                                                  share|improve this answer












                                                                                  share|improve this answer



                                                                                  share|improve this answer










                                                                                  answered Sep 23 '15 at 6:57









                                                                                  Max SherbakovMax Sherbakov

                                                                                  34928




                                                                                  34928













                                                                                  • Note that most of the content of this answer (i.e. the bit that's in comprehensible English) is copied from the MongoDB cookbook at the GitHub link the answerer provided.

                                                                                    – Mark Amery
                                                                                    Oct 5 '15 at 14:23



















                                                                                  • Note that most of the content of this answer (i.e. the bit that's in comprehensible English) is copied from the MongoDB cookbook at the GitHub link the answerer provided.

                                                                                    – Mark Amery
                                                                                    Oct 5 '15 at 14:23

















                                                                                  Note that most of the content of this answer (i.e. the bit that's in comprehensible English) is copied from the MongoDB cookbook at the GitHub link the answerer provided.

                                                                                  – Mark Amery
                                                                                  Oct 5 '15 at 14:23





                                                                                  Note that most of the content of this answer (i.e. the bit that's in comprehensible English) is copied from the MongoDB cookbook at the GitHub link the answerer provided.

                                                                                  – Mark Amery
                                                                                  Oct 5 '15 at 14:23











                                                                                  -4














                                                                                  We can merge two collection by using mongoDB sub query. Here is example,
                                                                                  Commentss--



                                                                                  `db.commentss.insert([
                                                                                  { uid:12345, pid:444, comment:"blah" },
                                                                                  { uid:12345, pid:888, comment:"asdf" },
                                                                                  { uid:99999, pid:444, comment:"qwer" }])`


                                                                                  Userss--



                                                                                  db.userss.insert([
                                                                                  { uid:12345, name:"john" },
                                                                                  { uid:99999, name:"mia" }])


                                                                                  MongoDB sub query for JOIN--



                                                                                  `db.commentss.find().forEach(
                                                                                  function (newComments) {
                                                                                  newComments.userss = db.userss.find( { "uid": newComments.uid } ).toArray();
                                                                                  db.newCommentUsers.insert(newComments);
                                                                                  }
                                                                                  );`


                                                                                  Get result from newly generated Collection--



                                                                                  db.newCommentUsers.find().pretty()


                                                                                  Result--



                                                                                  `{
                                                                                  "_id" : ObjectId("5511236e29709afa03f226ef"),
                                                                                  "uid" : 12345,
                                                                                  "pid" : 444,
                                                                                  "comment" : "blah",
                                                                                  "userss" : [
                                                                                  {
                                                                                  "_id" : ObjectId("5511238129709afa03f226f2"),
                                                                                  "uid" : 12345,
                                                                                  "name" : "john"
                                                                                  }
                                                                                  ]
                                                                                  }
                                                                                  {
                                                                                  "_id" : ObjectId("5511236e29709afa03f226f0"),
                                                                                  "uid" : 12345,
                                                                                  "pid" : 888,
                                                                                  "comment" : "asdf",
                                                                                  "userss" : [
                                                                                  {
                                                                                  "_id" : ObjectId("5511238129709afa03f226f2"),
                                                                                  "uid" : 12345,
                                                                                  "name" : "john"
                                                                                  }
                                                                                  ]
                                                                                  }
                                                                                  {
                                                                                  "_id" : ObjectId("5511236e29709afa03f226f1"),
                                                                                  "uid" : 99999,
                                                                                  "pid" : 444,
                                                                                  "comment" : "qwer",
                                                                                  "userss" : [
                                                                                  {
                                                                                  "_id" : ObjectId("5511238129709afa03f226f3"),
                                                                                  "uid" : 99999,
                                                                                  "name" : "mia"
                                                                                  }
                                                                                  ]
                                                                                  }`


                                                                                  Hope so this will help.






                                                                                  share|improve this answer



















                                                                                  • 7





                                                                                    Why did you basically copy this nearly identical, one-year-old answer? stackoverflow.com/a/22739813/4186945

                                                                                    – hackel
                                                                                    May 5 '15 at 20:45
















                                                                                  -4














                                                                                  We can merge two collection by using mongoDB sub query. Here is example,
                                                                                  Commentss--



                                                                                  `db.commentss.insert([
                                                                                  { uid:12345, pid:444, comment:"blah" },
                                                                                  { uid:12345, pid:888, comment:"asdf" },
                                                                                  { uid:99999, pid:444, comment:"qwer" }])`


                                                                                  Userss--



                                                                                  db.userss.insert([
                                                                                  { uid:12345, name:"john" },
                                                                                  { uid:99999, name:"mia" }])


                                                                                  MongoDB sub query for JOIN--



                                                                                  `db.commentss.find().forEach(
                                                                                  function (newComments) {
                                                                                  newComments.userss = db.userss.find( { "uid": newComments.uid } ).toArray();
                                                                                  db.newCommentUsers.insert(newComments);
                                                                                  }
                                                                                  );`


                                                                                  Get result from newly generated Collection--



                                                                                  db.newCommentUsers.find().pretty()


                                                                                  Result--



                                                                                  `{
                                                                                  "_id" : ObjectId("5511236e29709afa03f226ef"),
                                                                                  "uid" : 12345,
                                                                                  "pid" : 444,
                                                                                  "comment" : "blah",
                                                                                  "userss" : [
                                                                                  {
                                                                                  "_id" : ObjectId("5511238129709afa03f226f2"),
                                                                                  "uid" : 12345,
                                                                                  "name" : "john"
                                                                                  }
                                                                                  ]
                                                                                  }
                                                                                  {
                                                                                  "_id" : ObjectId("5511236e29709afa03f226f0"),
                                                                                  "uid" : 12345,
                                                                                  "pid" : 888,
                                                                                  "comment" : "asdf",
                                                                                  "userss" : [
                                                                                  {
                                                                                  "_id" : ObjectId("5511238129709afa03f226f2"),
                                                                                  "uid" : 12345,
                                                                                  "name" : "john"
                                                                                  }
                                                                                  ]
                                                                                  }
                                                                                  {
                                                                                  "_id" : ObjectId("5511236e29709afa03f226f1"),
                                                                                  "uid" : 99999,
                                                                                  "pid" : 444,
                                                                                  "comment" : "qwer",
                                                                                  "userss" : [
                                                                                  {
                                                                                  "_id" : ObjectId("5511238129709afa03f226f3"),
                                                                                  "uid" : 99999,
                                                                                  "name" : "mia"
                                                                                  }
                                                                                  ]
                                                                                  }`


                                                                                  Hope so this will help.






                                                                                  share|improve this answer



















                                                                                  • 7





                                                                                    Why did you basically copy this nearly identical, one-year-old answer? stackoverflow.com/a/22739813/4186945

                                                                                    – hackel
                                                                                    May 5 '15 at 20:45














                                                                                  -4












                                                                                  -4








                                                                                  -4







                                                                                  We can merge two collection by using mongoDB sub query. Here is example,
                                                                                  Commentss--



                                                                                  `db.commentss.insert([
                                                                                  { uid:12345, pid:444, comment:"blah" },
                                                                                  { uid:12345, pid:888, comment:"asdf" },
                                                                                  { uid:99999, pid:444, comment:"qwer" }])`


                                                                                  Userss--



                                                                                  db.userss.insert([
                                                                                  { uid:12345, name:"john" },
                                                                                  { uid:99999, name:"mia" }])


                                                                                  MongoDB sub query for JOIN--



                                                                                  `db.commentss.find().forEach(
                                                                                  function (newComments) {
                                                                                  newComments.userss = db.userss.find( { "uid": newComments.uid } ).toArray();
                                                                                  db.newCommentUsers.insert(newComments);
                                                                                  }
                                                                                  );`


                                                                                  Get result from newly generated Collection--



                                                                                  db.newCommentUsers.find().pretty()


                                                                                  Result--



                                                                                  `{
                                                                                  "_id" : ObjectId("5511236e29709afa03f226ef"),
                                                                                  "uid" : 12345,
                                                                                  "pid" : 444,
                                                                                  "comment" : "blah",
                                                                                  "userss" : [
                                                                                  {
                                                                                  "_id" : ObjectId("5511238129709afa03f226f2"),
                                                                                  "uid" : 12345,
                                                                                  "name" : "john"
                                                                                  }
                                                                                  ]
                                                                                  }
                                                                                  {
                                                                                  "_id" : ObjectId("5511236e29709afa03f226f0"),
                                                                                  "uid" : 12345,
                                                                                  "pid" : 888,
                                                                                  "comment" : "asdf",
                                                                                  "userss" : [
                                                                                  {
                                                                                  "_id" : ObjectId("5511238129709afa03f226f2"),
                                                                                  "uid" : 12345,
                                                                                  "name" : "john"
                                                                                  }
                                                                                  ]
                                                                                  }
                                                                                  {
                                                                                  "_id" : ObjectId("5511236e29709afa03f226f1"),
                                                                                  "uid" : 99999,
                                                                                  "pid" : 444,
                                                                                  "comment" : "qwer",
                                                                                  "userss" : [
                                                                                  {
                                                                                  "_id" : ObjectId("5511238129709afa03f226f3"),
                                                                                  "uid" : 99999,
                                                                                  "name" : "mia"
                                                                                  }
                                                                                  ]
                                                                                  }`


                                                                                  Hope so this will help.






                                                                                  share|improve this answer













                                                                                  We can merge two collection by using mongoDB sub query. Here is example,
                                                                                  Commentss--



                                                                                  `db.commentss.insert([
                                                                                  { uid:12345, pid:444, comment:"blah" },
                                                                                  { uid:12345, pid:888, comment:"asdf" },
                                                                                  { uid:99999, pid:444, comment:"qwer" }])`


                                                                                  Userss--



                                                                                  db.userss.insert([
                                                                                  { uid:12345, name:"john" },
                                                                                  { uid:99999, name:"mia" }])


                                                                                  MongoDB sub query for JOIN--



                                                                                  `db.commentss.find().forEach(
                                                                                  function (newComments) {
                                                                                  newComments.userss = db.userss.find( { "uid": newComments.uid } ).toArray();
                                                                                  db.newCommentUsers.insert(newComments);
                                                                                  }
                                                                                  );`


                                                                                  Get result from newly generated Collection--



                                                                                  db.newCommentUsers.find().pretty()


                                                                                  Result--



                                                                                  `{
                                                                                  "_id" : ObjectId("5511236e29709afa03f226ef"),
                                                                                  "uid" : 12345,
                                                                                  "pid" : 444,
                                                                                  "comment" : "blah",
                                                                                  "userss" : [
                                                                                  {
                                                                                  "_id" : ObjectId("5511238129709afa03f226f2"),
                                                                                  "uid" : 12345,
                                                                                  "name" : "john"
                                                                                  }
                                                                                  ]
                                                                                  }
                                                                                  {
                                                                                  "_id" : ObjectId("5511236e29709afa03f226f0"),
                                                                                  "uid" : 12345,
                                                                                  "pid" : 888,
                                                                                  "comment" : "asdf",
                                                                                  "userss" : [
                                                                                  {
                                                                                  "_id" : ObjectId("5511238129709afa03f226f2"),
                                                                                  "uid" : 12345,
                                                                                  "name" : "john"
                                                                                  }
                                                                                  ]
                                                                                  }
                                                                                  {
                                                                                  "_id" : ObjectId("5511236e29709afa03f226f1"),
                                                                                  "uid" : 99999,
                                                                                  "pid" : 444,
                                                                                  "comment" : "qwer",
                                                                                  "userss" : [
                                                                                  {
                                                                                  "_id" : ObjectId("5511238129709afa03f226f3"),
                                                                                  "uid" : 99999,
                                                                                  "name" : "mia"
                                                                                  }
                                                                                  ]
                                                                                  }`


                                                                                  Hope so this will help.







                                                                                  share|improve this answer












                                                                                  share|improve this answer



                                                                                  share|improve this answer










                                                                                  answered Mar 24 '15 at 9:27









                                                                                  KrishnaKrishna

                                                                                  11




                                                                                  11








                                                                                  • 7





                                                                                    Why did you basically copy this nearly identical, one-year-old answer? stackoverflow.com/a/22739813/4186945

                                                                                    – hackel
                                                                                    May 5 '15 at 20:45














                                                                                  • 7





                                                                                    Why did you basically copy this nearly identical, one-year-old answer? stackoverflow.com/a/22739813/4186945

                                                                                    – hackel
                                                                                    May 5 '15 at 20:45








                                                                                  7




                                                                                  7





                                                                                  Why did you basically copy this nearly identical, one-year-old answer? stackoverflow.com/a/22739813/4186945

                                                                                  – hackel
                                                                                  May 5 '15 at 20:45





                                                                                  Why did you basically copy this nearly identical, one-year-old answer? stackoverflow.com/a/22739813/4186945

                                                                                  – hackel
                                                                                  May 5 '15 at 20:45





                                                                                  protected by Community Jun 29 '15 at 9:46



                                                                                  Thank you for your interest in this question.
                                                                                  Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).



                                                                                  Would you like to answer one of these unanswered questions instead?



                                                                                  這個網誌中的熱門文章

                                                                                  Xamarin.form Move up view when keyboard appear

                                                                                  Post-Redirect-Get with Spring WebFlux and Thymeleaf

                                                                                  Anylogic : not able to use stopDelay()