I am trying to filter data after the lookup operator. I am not getting the expected behaviour out of my query.

My gateway collection is

{ "_id" : "18001887", "mac_id" : "18001887", group_id: "0" }
{ "_id" : "18001888", "mac_id" : "18001888", group_id: "1" }
{ "_id" : "18001889", "mac_id" : "18001889", group_id: "0" }

My commands collection is

{
    "_id" : ObjectId("615581dcb9ebca6c37eb39e4"),
    "org_id" : 0,
    "mac_id" : "18001887",
    "config" : {
        "user_info" : [ 
            {
                "user_id" : 1,
                "user_pwd" : "123456",
                "mapped_id" : 1
            }, 
            {
                "user_id" : 2,
                "user_pwd" : "123123",
                "mapped_id" : 3
            }
        ]
    }
}

{
    "_id" : ObjectId("615581dcb9ebca6c37eb39e4"),
    "org_id" : 0,
    "mac_id" : "18001889",
    "config" : {
        "slave_id" : 1 
    }
}

I want to fetch the commands of gateways with group_id = 0 and "config.user_info.mapped_id" = 1. I wrote the below query but it doesn't seem to work

gateway_model.aggregate([
            {
                $match: {
                    group_id: "0"
                },
                
            },
            {
                $project: {
                    _id: 0,
                    mac_id: 1
                }
            },
            {
                $lookup: {
                    from: "commands",
                    localField: "mac_id",
                    foreignField: "mac_id",
                    as: "childs"
                    
                }
            },
            { 
                $project: {
                    mac_id: 1,
                    childs: {
                        $filter: {
                            "input": "$childs",
                            "as": "child",
                            "cond": {"$eq": ["$$child.config.user_info.mapped_id", 1]},
                        }
                    }
                }
            }
        ])

Above query returns gateways with group_id 0 and childs is an empty array.

CodePudding user response：

The field user_info is array and you are checking equal-to condition in $filter operation, You can change your $filter condition as per below,

• When we access mapped_id from array field $$child.config.user_info.mapped_id, it will return array of ids so we need to use $in condition
• $ifNull to check if user_info field is not present then it will return blank array
• $in operator to check is 1 in mapped_id's array

  {
    $project: {
      mac_id: 1,
      childs: {
        $filter: {
          "input": "$childs",
          "as": "child",
          "cond": {
            "$in": [
              1,
              { $ifNull: ["$$child.config.user_info.mapped_id", []] }
            ]
          }
        }
      }
    }
  }

Playground

The second option and this is right way to handle this situation, $lookup using pipeline,

• let to pass mac_id to pipeline
• check $expr condition for mac_id
• match mapped_id condition

db.gateway.aggregate([
  { $match: { group_id: "0" } },
  {
    $lookup: {
      from: "commands",
      let: { mac_id: "$mac_id" },
      pipeline: [
        {
          $match: {
            $expr: { $eq: ["$mac_id", "$$mac_id"] },
            "config.user_info.mapped_id": 1
          }
        }
      ],
      as: "childs"
    }
  },
  {
    $project: {
      _id: 0,
      mac_id: 1,
      childs: 1
    }
  }
])

Playground

If you want to filter user_info array then you can add one more stage after $match stage in $lookup stage,

{
  $addFields: {
    "config.user_info": {
      $filter: {
        input: "$config.user_info",
        cond: { $eq: ["$$this.mapped_id", 1] }
      }
    }
  }
}

Playground