How to match an array field using lookup in mongodb?

Question

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.

Seems very similar to this https://stackoverflow.com/questions/34967482/lookup-on-objectids-in-an-array — Plancke, May 25 '19 at 12:01

score 2 · Accepted Answer · answered May 25 '19 at 12:01

2

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

answered May 25 '19 at 12:01

Ashh

44,693
14
105
132

How can i do the group the sub_menu. Its coming multiple times? – user1187 May 25 '19 at 17:37
i did the grouping. {"$group" : { _id : { 'sub_menus': '$sub_menus'}} } }. – user1187 May 26 '19 at 10:51

How to match an array field using lookup in mongodb?

1 Answers1