Filter on lookup collection in MongoDB Aggregation

user_5627012 0
Viewed 126

I have the following data

db={
  "worspace": [
    {
      "_id": 1,
      "admins": [
        "a1",
        "a2"
      ]
    },
    {
      "_id": 2,
      "admins": [
        "a3",
        "a1"
      ]
    },
    {
      "_id": 3,
      "admins": [
        "a3",
        "a2"
      ]
    }
  ],
  "users": [
    {
      "_id": "a1",
      "sku": "almonds",
      "description": "product 1",
      "instock": 120
    },
    {
      "_id": "a2",
      "sku": "bread",
      "description": "product 2",
      "instock": 80
    },
    {
      "_id": "a3",
      "sku": "cashews",
      "description": "product 3",
      "instock": 60
    },
    {
      "_id": "a6",
      "sku": "pecans",
      "description": "product 4",
      "instock": 70
    },
    {
      "_id": "a7",
      "sku": null,
      "description": "Incomplete"
    }
  ]
}

I want to find only those users who are in the workspace collection under admins field. I tried with the below query but no success

db.users.aggregate([
  {
    $lookup: {
      from: "worspace",
      pipeline: [
        {
          $match: {
            $expr: {
              $ne: [
                "$admins",
                "null"
              ]
            }
          }
        },
        {
          "$unwind": "$admins"
        },
        {
          $group: {
            _id: null,
            allAdmins: {
              "$addToSet": "$admins"
            }
          }
        }
      ],
      as: "workspaces"
    }
  },
  {
    $match: {
      "workspaces.allAdmins": "$_id"
    }
  }
])

https://mongoplayground.net/p/DKeNZ4Em6Ag

aggregation-framework mongodb
1 Answers
  • just try simple $lookup
  • $match if result not equal to empty array
db.users.aggregate([
  {
    $lookup: {
      from: "worspace",
      localField: "_id",
      foreignField: "admins",
      as: "workspaces"
    }
  },
  { $match: { workspaces: { $ne: [] } } }
])

Playground

user_89871280
Related