CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
user1187
user1187

Reputation: 2188

How to match an array field using lookup in mongodb?

I have two collection one is for storing the menu details and the other is for storing project details The below is the 'allmenu' collection. 

{
    "_id" : ObjectId("5ce7454f77af2d1143f84c38"),
    "menu_name" : "mainmenu1",
    "sub_menus" : [ 
        "submenu1",
        "submenu2"
    ]
}

The below is the 'project' collection. 

{
    "_id" : ObjectId("5cda19d2e7bb82e771931adb"),
    "project_name" : "p1",
    "sub_menus" : "submenu1",

}
{
    "_id" : ObjectId("5cda19d2e7bb82e771931adb"),
    "project_name" : "p2",
    "sub_menus" : "submenu2",

}

I want the result like below

  {
    "_id" : ObjectId("5ce7454f77af2d1143f84c35"),
    "menu_name" : "mainmenu1",
    "sub_menus" : [
      {"sub_menu_name" : "submenu1",
       "projectData" : [ 
        {
            "project_name" : "p1",            
        }
       ]
      }
    ]  
}

I tried the below query

db.getCollection('allmenu').aggregate([
{"$unwind":"$sub_menus"},
{"$lookup":{
        "from" :"project",
        "localField":"sub_menus",
        "foreignField":"sub_menus",
        "as":"newData"
        }
}])

I checked all the array lookup related questions. But all the questions are different from this.

Upvotes: 2

Views: 55

Answers (1)

Ashh
Ashh

Reputation: 46451

You can use below aggregation

db.collection.aggregate([
  { "$lookup": {
    "from": "project",
    "let": { "sub_menus": "$sub_menus" },
    "pipeline": [
      { "$match": { "$expr": { "$in": ["$sub_menus", "$$sub_menus"] }}},
      { "$project": {
        "projectData": [{ "project_name": "$project_name" }],
        "sub_menu_name": "$sub_menus"
      }}
    ],
    "as": "sub_menus"
  }}
])

MongoPlayground

Upvotes: 2

Related Questions