Home > Net >  How to filter a nested array in lookup aggregate query in mongodb
How to filter a nested array in lookup aggregate query in mongodb

Time:11-23

    db.magazine.aggregate([
        {
            $lookup: {
                from: 'pages',
                localField: 'typeId',
                foreignField: 'visibleTo._id',
                as: 'List'
            }
        }
    ])

Here I have a nested array in 'visbleTo' field. When I lookup into the pages collection. Now I'm getting all objects from 'visibleTo' field. Here I have to filter only matched objects from 'visibleTo._id'.

This is my current result

{ 
    "_id" : ObjectId("635f6e6902698132d9659c5a"), 
    "name" : "Sports Magazine", 
    "typeId" : ObjectId("635fc99652ecf9a969eed777"),
    "List" : [
        {
            "_id" : ObjectId("63748c1241ccfbc85a49af20"), 
            "pagename" : "cover", 
            "visibleTo" : [
                {
                    "checked" : "No", 
                    "name" : "Media",
                    "_id" : ObjectId("635fc99652ecf9a969eed777")
                }, 
                {
                    "checked" : "Yes",
                    "name" : "GHS", 
                    "_id" : ObjectId("635fc99652ecf9a969eed77c")
                }
            ]
        }, 
        {
            "_id" : ObjectId("637493810ca44ad4d564e7b3"), 
            "pagename" : "back", 
            "visibleTo" : [
                {
                    "checked" : "Yes",  
                    "name" : "Media",
                    "_id" : ObjectId("635fc99652ecf9a969eed777")
                }, 
                {
                    "checked" : "Yes", 
                    "name" : "GHS", 
                    "_id" : ObjectId("635fc99652ecf9a969eed77c")
                }, 
                {
                    "checked" : "Yes", 
                    "name" : "Others",
                    "_id" : ObjectId("635fc99652ecf9a969eed781")
                }
            ],
        }
    ]
}

CodePudding user response:

There are a few approaches here. The one I took is to leverage the ability to also use a nested pipeline inside of the $lookup (5.0). Specifically I added a $filter to remove the unwanted entries from the visibleTo array as they are retrieved. The additions to the $lookup are:

      let: {
        typeId: "$typeId"
      },
      pipeline: [
        {
          "$addFields": {
            "visibleTo": {
              "$filter": {
                "input": "$visibleTo",
                "cond": {
                  $eq: [
                    "$$this._id",
                    "$$typeId"
                  ]
                }
              }
            }
          }
        }
      ]

Full playground example here

  • Related