Mongoose aggregate $LOOKUP not displaying the data from table 2

Question

After reading mongodb mongoose docs, read many online examples, I tried to create two collections to test my own aggregate $lookup. I'm getting no error when I run the code but I display data from one table, not the joined 2nd collection. Not sure why but it must be something I'm doing because when I read about it, it's not complicated ;)

Here is my schema-model code :

var Schema = mongoose.Schema;
// create a schema
var locationSchema = new Schema({
    locationname: String,
    locationdescription: String,
    locationzip: Number,
    locationphone: Number,
    locationcategories: [{ locationname : String, categoryorder: Number, categoryname: 
    String, categorydescription: String, categoryitems: [] }],
    items: [{categoryid : String,  itemorder: Number, itemname: String, itemdescription:     
    String, itemprice: Number }],
    created_at: Date
  });

   var optionsSchema = new Schema({

    locationname: String,
    admindescription: String,
    headername: String,
    headerdescription: String,
    Options: [{ locationname : String, optionheadername : String, optionname: String, 
     optionprice: Number }],
    items: [{ itemid: String }],
    categories: [{ categoryid: String }]
});

 var Hoptions = mongoose.model('Hoptions', optionsSchema);   

var Location = mongoose.model('Location', locationSchema);
export default Location;
export {  Hoptions };

The location model (Location.find({}).pretty() ) display the following:

    {
        "_id" : ObjectId("5f8b96103f187f3f8050d863"),
        "locationname" : "location 1",
        "locationdescription" : "location One",
        "locationstreet" : "15588 main street",
        "locationcity" : "Naples",
        "locationstate" : "Florida",
        "locationzip" : 10090,
        "locationphone" : 8889788564,
        "locationcategories" : [
                {
                        "categoryitems" : [
                                {
                                        "categoryid" : "5f8b963c3f187f3f8050d864",
                                        "itemorder" : "1",
                                        "itemname" : "item1",
                                        "itemdescription" : "item1-cat1-location1",
                                        "itemprice" : "1"
                                },
                                {
                                        "categoryid" : "5f8b963c3f187f3f8050d864",
                                        "itemorder" : "0",
                                        "itemname" : "item2",
                                        "itemdescription" : "item2-cat1-location1",
                                        "itemprice" : "2"
                                }
                        ],
                        "_id" : ObjectId("5f8b963c3f187f3f8050d864"),
                        "locationname" : "location 1",
                        "categoryorder" : 1,
                        "categoryname" : "cat1",
                        "categorydescription" : "cat1-location1"
                },
                {
                        "categoryitems" : [ ],
                        "_id" : ObjectId("5f8b965d3f187f3f8050d865"),
                        "locationname" : "location 1",
                        "categoryorder" : 2,
                        "categoryname" : "cat2",
                        "categorydescription" : "cat2-location1"
                }
        ],
        "items" : [ ],
        "created_at" : ISODate("2020-10-18T01:10:40.206Z"),
        "__v" : 0
}

My Hoptions model display the following :

    {
        "_id" : ObjectId("5f8b973e3f187f3f8050d866"),
        "locationname" : "location 1",
        "admindescription" : "pizza options",
        "headername" : " Pizza Size",
        "headerdescription" : "Choose your pizza size:",
        "Options" : [
                {
                        "_id" : ObjectId("5f8b976a3f187f3f8050d867"),
                        "locationname" : "location 1",
                        "optionheadername" : " Pizza Size",
                        "optionname" : "Small Pizza",
                        "optionprice" : 10
                },
                {
                        "_id" : ObjectId("5f8b97793f187f3f8050d868"),
                        "locationname" : "location 1",
                        "optionheadername" : " Pizza Size",
                        "optionname" : "Medium Pizza",
                        "optionprice" : 15
                },
                {
                        "_id" : ObjectId("5f8b97843f187f3f8050d869"),
                        "locationname" : "location 1",
                        "optionheadername" : " Pizza Size",
                        "optionname" : "Large Pizza",
                        "optionprice" : 20
                }
        ],
        "items" : [ ],
        "categories" : [ ],
        "__v" : 0
}

Now, I use aggregate and $lookup :

Hoptions.aggregate([
    
    {
        $lookup:

// "locations" is the mongodb collection name. "Location" is my model name //Should I use the model name "Location" or the database "locations" in $lookup part? { from: "locations", localField: "locationname", foreignField: "locationname", as: "displayall" }

    },
   


    {
        $project:
        {

            "headername": 1,
            "locationzip": 1,
            "locationzip": 1,

        }
    }

], function (err, results) {
    console.log("results :", results)
    });

What I get is this:

results : [ { _id: 5f8b973e3f187f3f8050d866, headername: ' Pizza Size' } ]

"headername" is from hoptions collections (Hoption Model) but nothing from the locations collections (Location Model) which has the locationzip.

Clearly I'm new to mongodb aggregate pipeline and I appreciate if someone take a look and guide me to my mistake.

wak786 wak786 · Accepted Answer · 2020-10-19T05:43:08

Try this query-

db.Hoptions.aggregate([
  {
    "$lookup": {
      from: "locations",
      localField: "locationname",
      foreignField: "locationname",
      as: "displayall"
    }
  },
  {
    $project: {
      "headername": 1,
      "locationzip": "$displayall.locationzip"
    }
  }
])

Here is Mongo Playground

Hope this is what you were looking for. Let me know if the expected output is not correct.

Mongoose aggregate $LOOKUP not displaying the data from table 2

1 Answers