Mongodb 聚合查找以在每个数组中添加带有条件的字段

Question

我有3个收藏。
用户:

{
   "_id":ObjectId("60a495cdd4ba8b122899d415"),
   "email":"br9@gmail.com",
   "username":"borhan"
}

控制板:

{
   "_id": ObjectId("60a495cdd4ba8b122899d417"),
   "name": "borhan",
   "users": [
      {
          "role": "admin",
          "joined": "2021-05-19T04:35:47.474Z",
          "status": "active",
          "_id": ObjectId("60a495cdd4ba8b122899d418"),
          "user": ObjectId("60a495cdd4ba8b122899d415")
      },
      {
          "role": "member",
          "joined": "2021-05-19T04:35:47.474Z",
          "status": "active",
          "_id": ObjectId("60a49600d4ba8b122899d41a"),
          "user": ObjectId("60a34e167958972d7ce6f966")
       }
    ],
}

团队:

{
   "_id":ObjectId("60a495e0d4ba8b122899d419"),
   "title":"New Teams",
   "users":[
      ObjectId("60a495cdd4ba8b122899d415")
   ],
   "panel":ObjectId("60a495cdd4ba8b122899d417")
}

我想从查询面板集合中接收输出，就像这样:

{
   "_id": ObjectId("60a495cdd4ba8b122899d417"),
   "name": "borhan",
   "users": [
      {
          "role": "admin",
          "joined": "2021-05-19T04:35:47.474Z",
          "status": "active",
          "_id": ObjectId("60a495cdd4ba8b122899d418"),
          "user": ObjectId("60a495cdd4ba8b122899d415"),
          "teams":[
             {
                 "_id":ObjectId("60a495e0d4ba8b122899d419"),
                 "title":"New Teams",
                 "users":[
                      ObjectId("60a495cdd4ba8b122899d415")
                  ],
                 "panel":ObjectId("60a495cdd4ba8b122899d417")
             }
          ]
      },
      {
          "role": "member",
          "joined": "2021-05-19T04:35:47.474Z",
          "status": "active",
          "_id": ObjectId("60a49600d4ba8b122899d41a"),
          "user": ObjectId("60a34e167958972d7ce6f966")
       }
    ],
}

我的意思是我想向 Panel 集合中的每个用户添加团队字段(这是用户所在的团队数组)
这是我在 Mongoose 中选择特定面板的匹配查询:

panel_model.aggregate([
   {
      $match: {              
         users: {
            $elemMatch: {user: ObjectId("60a495cdd4ba8b122899d415"), role:"admin"}
         }
      }
   },
])

是否可以使用 $lookup 或 $addFields 聚合来获取我的输出？

Answer 1

你需要加入所有三个集合，

$unwind解构数组

$lookup有两种查找有助于加入集合。首先我用了 Multiple-join-conditions-with--lookup ，我使用标准查找加入 Users和 Teams集合。

$match匹配用户 ID

$expr - 当您使用 $match内部查找，您必须使用它。

$set添加新字段

$group我们已经使用 $unwind 进行了破坏.不，我们需要重构它

这是代码

db.Panel.aggregate([
  { $unwind: "$users" },
  {
    "$lookup": {
      "from": "User",
      "let": { uId: "$users.user" },
      "pipeline": [
        {
          $match: {
            $expr: {
              $eq: [ "$_id", "$$uId" ]
            }
          }
        },
        {
          "$lookup": {
            "from": "Team",
            "localField": "_id",
            "foreignField": "users",
            "as": "teams"
          }
        }
      ],
      "as": "users.join"
    }
  },
  {
    "$set": {
      "users.getFirstElem": {
        "$arrayElemAt": [ "$users.join", 0 ]
      }
    }
  },
  {
    $set: {
      "users.teams": "$users.getFirstElem.teams",
      "users.join": "$$REMOVE",
      "users.getFirstElem": "$$REMOVE"
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "name": { "$first": "name" },
      "users": { $push: "$users" }
    }
  }
])

工作Mongo playground
注意:希望面板和用户集合是 1-1 的关系。否则让我知道