MongoDB Compass: How can I perform a SQL join / Mongoose populate?

Question

I have a collection called Post, which has an ID field called makeupProduct. This ID field is a foreign key to my MakeupProduct collection.

In MongoDB Compass, I'm trying to find all Posts

where productUrl === null and
populate the Makeup record along with it

Is this possible?

I have the 1st filter down, but don't know how to write the rest.

You can use the Aggregation (Aggregations TAB in Compass) to do a _join_ between _related_ collections using the aggregation pipeline stage [$lookup](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/index.html). In Compass you will be able to query, and you may have to do additional processing to update the data. — prasad_, Feb 21 '20 at 02:59
Does this answer your question? [How to join multiple collections with $lookup in mongodb](https://stackoverflow.com/questions/35813854/how-to-join-multiple-collections-with-lookup-in-mongodb) , You check that link on how to use `$lookup` operator which is native to MongoDB. But in case if you're using mongoose driver from Node.Js code you can use `.populate()` which does the similar job. Try using **robo3T** which is free to use + better as well & gives you shell kind of feel. — whoami - fakeFaceTrueSoul, Feb 21 '20 at 03:29

score 3 · Accepted Answer · answered Feb 21 '20 at 05:50

3

db.Post.aggregate([
    {
        $match: {
            productUrl: null
        }
    },
    {
        $lookup: 
        {
            from: "MakeupProduct",
            localField: "makeupProduct",
            foreignField: "_id",
            as: "makeupProduct"
        }
    },
    {
        $set: {
            makeupProduct: {
                $arrayElemAt: ["$makeupProduct", 0]
            }
        }
    }
])

answered Feb 21 '20 at 05:50

Dĵ ΝιΓΞΗΛψΚ

5,068
3
13
26

What's the $set part for? – StudioWorks Aug 24 '21 at 20:20
1

@StudioWorks Because the `$lookup` pipe creates an array with all of the matching documents, and since it is only one document, the `$set` pipe sets the fields value to be the first element of the array. – Viriato Apr 05 '22 at 15:40

score 0 · Answer 2 · answered Jun 28 '23 at 10:42

Easy Way

In mongoose, the alternative is called populate(). This process automatically replacing (update) the specified path (schema Id) in the document, with a document from a different model. more Help

const { Schema, model} = require("mongoose");
const UserSchema = new Schema({
   name:{
      type: String,
      required: true
   },
   email:{
      type: String,
      required: true
   },
   posts:[{
      type: Schema.Types.ObjectId, ref: "Post"
   }]
});
const PostSchema = new Schema({
   title: String,
   desc: String,
   User: {type: Schema.Tpes.ObjectId, ref: "User"}
});
export const Post = model("Post", PostSchema);
export const User = model("User", UserSchema);

//----------

User.findOne({
   name: "Robert Look"
}).populate('posts').exec((err, user) =>{
   if(err){
      console.log(err)
   }else{
      console.log(users.posts[0].desc)
   }
});

MongoDB Compass: How can I perform a SQL join / Mongoose populate?

2 Answers2