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 stackoverflow.com/questions/34967482/… — Plancke
– Plancke, Commented May 25, 2019 at 12:01

Ashh · Accepted Answer · 2019-05-25 12:01:39Z

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, 2019 at 12:01

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1187 Over a year ago

How can i do the group the sub_menu. Its coming multiple times?

user1187 Over a year ago

i did the grouping. {"$group" : { _id : { 'sub_menus': '$sub_menus'}} } }.

Collectives™ on Stack Overflow

How to match an array field using lookup in mongodb?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related