CODEWITHSUNDEEP
mongodbaggregation-frameworklookup
user1187
user1187

Reputation: 2188

How can I do a lookup based on conditional selection?

I have 2 collection based on collection1 I need to fetch from collection2

collection1

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

based on project field I need to fetch the record. If the project field is "All", I need to fetch all the projects under that submenu. if it is specific project only those project I need to fetch.

Here is my collection2

collection2

 "project": [
{
  "project_name": "p1",
  "sub_menus": "submenu1",

},
{
  "project_name": "p2",
  "sub_menus": "submenu2",

}
{
  "project_name": "p2",
  "sub_menus": "submenu1",

},
{
  "project_name": "p3",
  "sub_menus": "submenu2",

}
{
  "project_name": "p3",
  "sub_menus": "submenu1",

},
{
  "project_name": "p4",
  "sub_menus": "submenu2",

}

]

https://mongoplayground.net/p/qH9fuJorq6z. Can I do a conditional lookup?

Expected Result is 

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

        "projectData": [
          {
            "project_name": "p2"
          }
        ],
        "sub_menu_name": "submenu2"
      }
    ]
  }
]

Upvotes: 1

Views: 57

Answers (1)

mickl
mickl

Reputation: 49945

Yes, you can define your own matching condition for $lookup pipeline but since your structure is deeply nested you need to flatten your sub_menus using $reduce before you run your $lookup. Once you bring all projects that match to any submenu you can use $map with $filter to put them into releval sub_menu:

db.collection1.aggregate([
    {
        $addFields: {
            sub_menus_flat: {
                $reduce: {
                    input: "$sub_menus",
                    initialValue: [],
                    in: {
                        $concatArrays: [ 
                            "$$value", 
                            { $map: { input: "$$this.project", as: "p", in: { name: "$$this.name", project: "$$p" } } } 
                        ]
                    }
                }
            }
        }
    },
    {
        $lookup: {
            from: "collection2",
            let: { sub_menus_flat: "$sub_menus_flat" },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $anyElementTrue: {
                                $map: {
                                    input: "$$sub_menus_flat",
                                    in: {
                                        $and: [
                                            { $eq: [ "$$this.name", "$sub_menus" ] },
                                            { $in: [ "$$this.project", [ "All", "$project_name" ] ] }
                                        ]
                                    }
                                }
                            }
                        }
                    }
                }
            ],
            as: "projects"
        }
    },
    {
        $project: {
            _id: 1,
            menu_name: 1,
            sub_menus: {
                $map: {
                    input: "$sub_menus",
                    in: {
                        sub_menu_name: "$$this.name",
                        projectData: {
                            $filter: {
                                input: "$projects",
                                as: "p",
                                cond: {
                                    $and: [
                                        { $eq: [ "$$p.sub_menus", "$$this.name" ] }
                                    ]
                                }
                            }
                        }
                    }
                }
            }
        }
    },
    {
        $project: {
            "sub_menus.projectData._id": 0,
            "sub_menus.projectData.sub_menus": 0
        }
    }
])

MongoDB Playground

Upvotes: 1

Related Questions