How to execute the equivalent SQL Join in MongoDB?

For example, say you have two sets (users and comments), and I want to extract all comments with pid = 444 and user information for each user.

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 extract all comments with specific fields at once (for example... find ({pid: 444})), as well as the user information associated with each comment?

At this point, I first need to get comments that meet my criteria, then find out all UIDs in the result set, get user objects, and merge them with the comment results. It's like I did something wrong.

#1 building

playORM can do this for you using S-SQL (scalable SQL), which only adds partitions so that you can join within them.

#2 building

You can run SQL queries, including the mongo_fdw On MongoDB Join .

#3 building

It depends on what you want to do.

Currently, you have set it up as a normalized database, which is good, and your operation mode is also suitable.

But there are other ways to do that.

You may have a collection of posts that contains comments for each post and references to users that you can iterate through. You can store user names with comments, or you can store them all in one document.

The advantage of NoSQL is that it is designed for flexible patterns and very fast read and write. In a typical big data farm, database is the biggest bottleneck, and there are fewer database engines than applications and front-end servers They are more expensive, but they are more powerful, and hard disk space is relatively cheap. Standardization comes from the concept of trying to save space, but it comes with the cost of making the database perform complex joins, verifying the integrity of relationships, and performing cascading operations. If the developers design the database correctly, all of this will save the developers from headaches.

With NoSQL, if you accept that redundancy and storage space are not a problem due to their cost (processor time required to perform the update and the cost of hard drives to store additional data), then denormalization is not a problem (tens of thousands of projects may be a performance problem for embedded arrays, but in most cases it is not). In addition, you will provide multiple applications and front-end servers for each database cluster. Let them bear the heavy connection work, let the database server insist on reading and writing.

TL; DR: what are you doing well? There are other ways to do it. Look at the data model patterns of mongodb documents for some good examples. http://docs.mongodb.org/manual/data-modeling/

#4 building

With the mongodb client console, we can merge / merge all the data in a collection with simple functions in just a few lines. Now we can perform the required queries. Here is a complete example,

- Author:

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

- Category:

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 borrowing

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'
    }
]);

. - 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 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 line can help you.

#5 building

You must follow the instructions. MongoDB is a non relational database and does not support joins.