Mongoose lookup fetching data from multi collection

Question

When i combine 2 table to fetch data from mongoDB collection struck with my expected out. please any one help me to fix the same pleas.

Collection:

recipecatagories

{
    "_id":{"$oid":"5dada3c5761bb32a1201d4da"},
    "CategoryName":"Biryani"
}
{
    "_id":{"$oid":"5dada3c5761bb32a1201d4db"},
    "CategoryName":"Mutton Biryani"
}
{
    "_id":{"$oid":"5dada3c5761bb32a1201d4d4"},
    "CategoryName":"Chicken Biryani"
}
{
    "_id":{"$oid":"5daea43a517cf601a7e80a3b"},
    "CategoryName":"Kathirikai gothsu"
}

recipes:

{
    "_id":{"$oid":"5daffda85d9b4fd19ae4da30"},
    "recipeTitle":"Mutton dum biryani",
    "Recipetags":["Indian","NonVeg","Lunch"],
    "cookTime":"30 Mins",
    "recipeCategoryId":[{"$oid":"5dada3c5761bb32a1201d4da"},{"$oid":"5dada3c5761bb32a1201d4db"},{"$oid":"5dada3c5761bb32a1201d4dc"}],
    "recipeCuisienId":"Indian",
    "recepeType":false,
    "availaleStreaming":"TEXT",
    "postedOn":{"$date":{"$numberLong":"0"}},
    "postedBy":"shiva@yopmail.com"
}
{
    "_id":{"$oid":"5daffda85d9b4fd19ae4da30"},
    "recipeTitle":"Mutton Chicken biryani",
    "Recipetags":["Indian","NonVeg","Lunch"],
    "cookTime":"30 Mins",
    "recipeCategoryId":[{"$oid":"5dada3c5761bb32a1201d4da"},{"$oid":"5dada3c5761bb32a1201d4d4"},{"$oid":"5dada3c5761bb32a1201d4dc"}],
    "recipeCuisienId":"Indian",
    "recepeType":false,
    "availaleStreaming":"TEXT",
    "postedOn":{"$date":{"$numberLong":"0"}},
    "postedBy":"shiva@yopmail.com"
}

users:

{
    "_id":{"$oid":"5da428b85e3cbd0f153c7f3b"},
    "emailId":"shiva@yopmail.com",
    "fullName":"siva prakash",
    "accessToken":"xxxxxxxxxxxxx",
    "__v":{"$numberInt":"0"}
}

Current mongoose code in node js

RecipeCatagory.aggregate([
        { "$match": { "_id": mongoose.Types.ObjectId(id) } },
        {
            "$lookup": {
                "from": "recipes",
                "localField": "_id",
                "foreignField": "recipeCategoryId",
                "as": "recipes"
            }
        },
        { "$unwind": "$recipes" },
        { "$unwind": "$recipes.recipeCategoryId" },
        {
            "$match": {
                "recipes.recipeCategoryId": mongoose.Types.ObjectId(id)
            }
        },
        {
            "$lookup": {
                "from": "users",
                "localField": "emailId",
                "foreignField": "recipes.postedBy",
                "as": "users"
            }
        },

    ])
        .exec(function (err, recipes) {
            if (err) {
                response
                    .status(400)
                    .json({
                        "status": "Failed",
                        "message": "Error",
                        "data": err | err.message
                    });
                return
            } else {
                response
                    .status(200)
                    .json({
                        "status": "Ok",
                        "message": "Success",
                        "data": recipes
                    });
                return
            }
        })

Current Output using above Query

{
    "status": "Ok",
    "message": "Success",
    "data": [
        {
            "_id": "5dada3c5761bb32a1201d4da",
            "CategoryName": "Biryani",
            "recipes": {
                "_id": "5daffda85d9b4fd19ae4da30",
                "recipeTitle": "Mutton dum biryani",
                "Recipetags": [
                    "Indian",
                    "NonVeg",
                    "Lunch"
                ],
                "cookTime": "30 Mins",
                "recipeCategoryId": "5dada3c5761bb32a1201d4da",
                "recipeCuisienId": "Indian",
                "recepeType": false,
                "availaleStreaming": "TEXT",
                "postedOn": "1970-01-01T00:00:00.000Z",
                "postedBy": "shiva@yopmail.com"
            },
            "users": [
                {
                    "_id": "5da428b85e3cbd0f153c7f3b",
                    "emailId": "shiva@yopmail.com",
                    "fullName": "siva prakash",
                    "accessToken": "42eb19a0-ee57-11e9-86f7-a7b758fb7271",
                    "__v": 0
                }
            ]
        },
        {
            "_id": "5dada3c5761bb32a1201d4da",
            "CategoryName": "Biryani",
            "recipes": {
                "_id": "5daffda85d9b4fd19ae4da31",
                "recipeTitle": "Kumbakonam kathirikai gothsu",
                "Recipetags": [
                    "Indian",
                    "Veg"
                ],
                "cookTime": "30 Mins",
                "recipeCategoryId": "5dada3c5761bb32a1201d4da",
                "recipeCuisienId": "Indian",
                "recepeType": true,
                "availaleStreaming": "TEXT",
                "postedOn": "1970-01-01T00:00:00.000Z",
                "postedBy": "shiva@yopmail.com"
            },
            "users": [
                {
                    "_id": "5da428b85e3cbd0f153c7f3b",
                    "emailId": "shiva@yopmail.com",
                    "fullName": "siva prakash",
                    "accessToken": "xxxxxxxxxxxxx",
                    "__v": 0
                }
            ]
        }
    ]
}


**Expected Out:**
    {
    "status": "Ok",
    "message": "Success",
    "data": [
        {
            "_id": "5dada3c5761bb32a1201d4da",
            "CategoryName": "chiken Biryani",
            "recipes": {
                "_id": "5daffda85d9b4fd19ae4da30",
                "recipeTitle": "Mutton dum biryani",
                "Recipetags": [
                    "Indian",
                    "NonVeg",
                    "Lunch"
                ],
                "cookTime": "30 Mins",
                "recipeCategoryId": "5dada3c5761bb32a1201d4da",
                "recipeCuisienId": "Indian",
                "recepeType": false,
                "availaleStreaming": "TEXT",
                "postedOn": "1970-01-01T00:00:00.000Z",
                "postedBy": "shiva@yopmail.com"
            },
            "users": [
                {
                    "_id": "5da428b85e3cbd0f153c7f3b",
                    "emailId": "shiva@yopmail.com",
                    "fullName": "siva prakash",
                    "accessToken": "42eb19a0-ee57-11e9-86f7-a7b758fb7271",
                    "__v": 0
                }
            ]
        },
        {
            "_id": "5dada3c5761bb32a1201d4da",
            "CategoryName": "Biryani",
            "recipes": [
                {
                "_id": "5daffda85d9b4fd19ae4da31",
                "recipeTitle": "Mutton dum biryani",
                "Recipetags": [
                    "Indian",
                    "Veg"
                ],
                "cookTime": "30 Mins",
                "recipeCategoryId": "5dada3c5761bb32a1201d4da",
                "recipeCuisienId": "Indian",
                "recepeType": true,
                "availaleStreaming": "TEXT",
                "postedOn": "1970-01-01T00:00:00.000Z",
                "postedBy": "shiva@yopmail.com"
                },
                {
                "_id": "5daffda85d9b4fd19ae4da31",
                "recipeTitle": "Chicken biryani",
                "Recipetags": [
                    "Indian",
                    "Veg"
                ],
                "cookTime": "30 Mins",
                "recipeCategoryId": "5dada3c5761bb32a1201d4da",
                "recipeCuisienId": "Indian",
                "recepeType": true,
                "availaleStreaming": "TEXT",
                "postedOn": "1970-01-01T00:00:00.000Z",
                "postedBy": "shiva@yopmail.com"
                }
            ],
            "users": [
                {
                    "_id": "5da428b85e3cbd0f153c7f3b",
                    "emailId": "shiva@yopmail.com",
                    "fullName": "siva prakash",
                    "accessToken": "xxxxxxxxxxxx",
                    "__v": 0
                }
            ]
        }
    ]
}

i am struck to get expected out put... i want recipes as array which has recipecategory has in recipe collection...

Can you add mongoose models (recipecatagories, recipes) to the question? — SuleymanSah, Nov 01 '19 at 13:04

score 1 · Accepted Answer · answered Nov 02 '19 at 02:48

You are basically doing this the wrong way around and should instead be querying from the Recipe model. You do already have the "category id values" which are contained within an array of that document.

Basically you should have something like this:

const wantedCategories = [
  ObjectId("5dada3c5761bb32a1201d4da"),
  ObjectId("5dada3c5761bb32a1201d4db")
];

let data = await Recipe.aggregate([
  // Match wanted category(ies)
  { "$match": {
    "recipeCategoryId": { "$in": wantedCategories }
  }},
  // Filter the content of the array
  { "$addFields": {
    "recipeCategoryId": {
      "$filter": {
        "input": "$recipeCategoryId",
        "cond": {
          "$in": [ "$$this", wantedCategories ]
        }
      }
    }
  }},
  // Lookup the related matching category(ies)
  { "$lookup": {
    "from": RecipeCategory.collection.name,
    "let": { "recipeCategoryIds": "$recipeCategoryId" },
    "pipeline": [
      { "$match": {
        "$expr": { "$in": [ "$_id", "$$recipeCategoryIds" ] }
      }}
    ],
    "as": "recipeCategoryId"
  }},
  // Lookup the related user to postedBy
  { "$lookup": {
    "from": User.collection.name,
    "let": { "postedBy": "$postedBy" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": [ "$emailId", "$$postedBy" ] } } }
    ],
    "as": "postedBy"
  }},
  // postedBy is "singular"
  { "$unwind": "$postedBy" }
]);

Which would return a result like this:

{
  "data": [
    {
      "_id": "5dbce992010163139853168c",
      "Recipetags": [
        "Indian",
        "NonVeg",
        "Lunch"
      ],
      "recipeCategoryId": [
        {
          "_id": "5dada3c5761bb32a1201d4da",
          "CategoryName": "Biryani",
          "__v": 0
        },
        {
          "_id": "5dada3c5761bb32a1201d4db",
          "CategoryName": "Mutton Biryani",
          "__v": 0
        }
      ],
      "recipeTitle": "Mutton dum biryani",
      "cookTime": "30 Mins",
      "recepeType": false,
      "postedBy": {
        "_id": "5dbce992010163139853168e",
        "emailId": "shiva@yopmail.com",
        "fullName": "siva prakash",
        "accessToken": "xxxxxxxxxxxxx",
        "__v": 0
      },
      "__v": 0
    },
    {
      "_id": "5dbce992010163139853168d",
      "Recipetags": [
        "Indian",
        "NonVeg",
        "Lunch"
      ],
      "recipeCategoryId": [
        {
          "_id": "5dada3c5761bb32a1201d4da",
          "CategoryName": "Biryani",
          "__v": 0
        }
      ],
      "recipeTitle": "Mutton Chicken biryani",
      "cookTime": "30 Mins",
      "recepeType": false,
      "postedBy": {
        "_id": "5dbce992010163139853168e",
        "emailId": "shiva@yopmail.com",
        "fullName": "siva prakash",
        "accessToken": "xxxxxxxxxxxxx",
        "__v": 0
      },
      "__v": 0
    }
  ]
}

Note: I do actually correct the english spelling of a model with RecipeCategory instead of RecipeCatagory as shown in the question. Apply to your own implementation as you wish.

You might note the usage of $in with a "list of ids" in both the query form and the aggregation operator form at different stages. Personally I would code this in this way even if there was only a single value supplied at the present time, since it means there would be little to change other than the input variable to the method in the event I wanted multiple values, such as "multiple categories" within a faceted search option for example.

So this demonstrates the "list" argument approach, though it still applies to singular values as in the question.

The whole process follows what the comments say on each pipeline stage, being that you first match wanted "documents" from the recipes collection by the selected "category" value(s). Then we just want to remove any non-matching data for the category within the array of those documents. This could actually be viewed as "optional" if you wanted to just show ALL categories associated with that recipe whether they matched the criteria or not. Where this is the case, all you need do is remove the stage containing the $filter statement, and the code will happily work in that way.

Then of course there are the $lookup stages, being one for each related collection. The example here actually shows the expressive form of the $lookup pipeline stage. This again is really only for demonstration as the standard localField and foreignField form is perfectly fine for the purposes of what you want to do here, and the further syntax is not needed. MongoDB will basically transform that older syntax into the newer expressive form as shown internally anyway.

You might note the usage of Model.collection.name in the from argument though. This is actually a handy thing to do when coding with mongoose. MongoDB itself expects the actual collection name as the argument here. Since mongoose will typically pluralize the model name provided for the actual collection referenced, or otherwise takes an explicit argument to the model definition, then using the .collection.name accessor from the model ensures you have the correct actual collection name, even if this changes at some time within the model definition.

The only other simple step here is the $unwind at the end, and only because the output of $lookup is always an array, and here the replacement of the postedBy property with matched related content is always expected to be only one item. So for simple readability of results, we can just make this a single value instead of having an array here.

For a bit more context into how that all comes together, here is the code for the statement and the creation of the data all in a self contained listing, from which of course the "output" posted above was actually obtained:

const { Schema, Types: { ObjectId } } = mongoose = require('mongoose');

const uri = 'mongodb://localhost:27017/menu';
const options = { useNewUrlParser: true, useUnifiedTopology: true };

mongoose.set('debug', true);
mongoose.set('useFindAndModify', false);
mongoose.set('useCreateIndex', true);

const recipeCategorySchema = new Schema({
  CategoryName: String
});

const recipeSchema = new Schema({
  recipeTitle: String,
  Recipetags: [String],
  cookTime: String,
  recipeCategoryId: [{ type: Schema.Types.ObjectId, ref: 'RecipeCategory' }],
  recipeCuisineId: String,
  recepeType: Boolean,
  availableStreaming: String,
  postedBy: String
});

const userSchema = new Schema({
  emailId: String,
  fullName: String,
  accessToken: String
});

const RecipeCategory = mongoose.model('RecipeCategory', recipeCategorySchema);
const Recipe = mongoose.model('Recipe', recipeSchema);
const User = mongoose.model('User', userSchema);

const log = data => console.log(JSON.stringify(data, undefined, 2));

(async function() {

  try {

    const conn = await mongoose.connect(uri, options);

    // Clean data for demonstration
    await Promise.all(
      Object.values(conn.models).map(m => m.deleteMany())
    );

    // Insert some data
    await RecipeCategory.insertMany([
      {
        "_id": ObjectId( "5dada3c5761bb32a1201d4da"),
        "CategoryName":"Biryani"
      },
      {
        "_id": ObjectId("5dada3c5761bb32a1201d4db"),
        "CategoryName":"Mutton Biryani"
      },
      {
        "_id": ObjectId("5dada3c5761bb32a1201d4d4"),
        "CategoryName":"Chicken Biryani"
      },
      {
        "_id": ObjectId("5daea43a517cf601a7e80a3b"),
        "CategoryName":"Kathirikai gothsu"
      }
    ]);

    await Recipe.insertMany([

      {
        "recipeTitle":"Mutton dum biryani",
        "Recipetags":["Indian","NonVeg","Lunch"],
        "cookTime":"30 Mins",
        "recipeCategoryId":[
          ObjectId("5dada3c5761bb32a1201d4da"),
          ObjectId("5dada3c5761bb32a1201d4db"),
          ObjectId("5dada3c5761bb32a1201d4dc")
        ],
        "recipeCuisienId":"Indian",
        "recepeType":false,
        "availaleStreaming":"TEXT",
        "postedOn": new Date(),
        "postedBy":"shiva@yopmail.com"
      },
      {
        "recipeTitle":"Mutton Chicken biryani",
        "Recipetags":["Indian","NonVeg","Lunch"],
        "cookTime":"30 Mins",
        "recipeCategoryId":[
          ObjectId("5dada3c5761bb32a1201d4da"),
          ObjectId("5dada3c5761bb32a1201d4d4"),
          ObjectId("5dada3c5761bb32a1201d4dc")
        ],
        "recipeCuisienId":"Indian",
        "recepeType":false,
        "availaleStreaming":"TEXT",
        "postedOn": new Date(),
        "postedBy":"shiva@yopmail.com"
      }
    ]);

    await User.create({
      "emailId":"shiva@yopmail.com",
      "fullName":"siva prakash",
      "accessToken":"xxxxxxxxxxxxx",
    });

    const wantedCategories = [
      ObjectId("5dada3c5761bb32a1201d4da"),
      ObjectId("5dada3c5761bb32a1201d4db")
    ];

    let data = await Recipe.aggregate([
      // Match wanted category(ies)
      { "$match": {
        "recipeCategoryId": { "$in": wantedCategories }
      }},
      // Filter the content of the array
      { "$addFields": {
        "recipeCategoryId": {
          "$filter": {
            "input": "$recipeCategoryId",
            "cond": {
              "$in": [ "$$this", wantedCategories ]
            }
          }
        }
      }},
      // Lookup the related matching category(ies)
      { "$lookup": {
        "from": RecipeCategory.collection.name,
        "let": { "recipeCategoryIds": "$recipeCategoryId" },
        "pipeline": [
          { "$match": {
            "$expr": { "$in": [ "$_id", "$$recipeCategoryIds" ] }
          }}
        ],
        "as": "recipeCategoryId"
      }},
      // Lookup the related user to postedBy
      { "$lookup": {
        "from": User.collection.name,
        "let": { "postedBy": "$postedBy" },
        "pipeline": [
          { "$match": { "$expr": { "$eq": [ "$emailId", "$$postedBy" ] } } }
        ],
        "as": "postedBy"
      }},
      // postedBy is "singular"
      { "$unwind": "$postedBy" }
    ]);

    log({ data });

  } catch (e) {
    console.error(e)
  } finally {
    mongoose.disconnect();
  }

})()

Thank you so much for the perfect solution.... You have proper explanation which are very user full for me... — Sivaprakash D, Nov 02 '19 at 05:24

Mongoose lookup fetching data from multi collection

1 Answers1