MongoDB按ObjectIds数组进行聚合匹配

Question

集合

我有一个具有以下数据的集合abyssBattles：

[
  {
    "floor_level": "12-2",
    "battle_index": 1,
    "party": [
      "60b6edd41ec85acb419565dc",
      "60b6edd41ec85acb419565e3",
      "60b6edd41ec85acb41956648",
      "60b6edd41ec85acb41956669"
    ]
  },
  {
    "floor_level": "12-3",
    "battle_index": 1,
    "party": [
      "60b6edd41ec85acb419565dc",
      "60b6edd41ec85acb419565e3",
      "60b6edd41ec85acb41956648",
      "60b6edd41ec85acb41956669"
    ]
  },
  {
    "floor_level": "12-1",
    "battle_index": 1,
    "party": [
      "60b6edd41ec85acb419565dc",
      "60b6edd41ec85acb419565e3",
      "60b6edd41ec85acb41956648",
      "60b6edd41ec85acb41956669"
    ]
  },
  {
    "floor_level": "12-1",
    "battle_index": 2,
    "party": [
      "60b6ee621ec85acb4195b0c6",
      "60b6ee621ec85acb4195b0d2",
      "60b6ee621ec85acb4195b0e8",
      "60ba56671ec85acb41045ff5"
    ]
  },
  {
    "floor_level": "12-2",
    "battle_index": 2,
    "party": [
      "60b6ee621ec85acb4195b0c6",
      "60b6ee621ec85acb4195b0d2",
      "60b6ee621ec85acb4195b0e8",
      "60ba56671ec85acb41045ff5"
    ]
  }
]

我主要是通过floor_level和party数组中的ObjectIds进行过滤。我试图在party包含完全相同的4 ObjectIds (已经排序)的地方聚合记录。我想按它们所属的floor_level和battle_index对它们进行分组，并得到party计数的总和。

期望结果

我想要达到的结果如下：

[
  { 
    floor: "12-1-1",
    parties: [
     { 
       party: [
          "60b6ee621ec85acb4195b0c6",
          "60b6ee621ec85acb4195b0d2",
          "60b6ee621ec85acb4195b0e8",
          "60ba56671ec85acb41045ff5"
       ],
       count: 23
     },
     { 
       party: [
        "60b6edd41ec85acb419565dc",
        "60b6edd41ec85acb419565e3",
        "60b6edd41ec85acb41956648",
        "60b6edd41ec85acb41956669"
       ],
       count: 13
     },
     ...
   ].
  },
  { 
    floor: "12-1-2",
    parties: [
     { 
       party: [
          "60b6ee621ec85acb4195b0c6",
          "60b6ee621ec85acb4195b0d2",
          "60b6ee621ec85acb4195b0e8",
          "60ba56671ec85acb41045ff5"
       ],
       count: 52
     },
     { 
       party: [
        "60b6edd41ec85acb419565dc",
        "60b6edd41ec85acb419565e3",
        "60b6edd41ec85acb41956648",
        "60b6edd41ec85acb41956669"
       ],
       count: 13
     },
     ...
   ]
  }
]

其中count表示为各自的floor找到的特定party的数量。每个floor都有一个数组中的多个party，每个party是4个ObjectId的唯一组合。

示例

假设这里有一个简化的集合版本：

[
  {
    "floor_level": "12-1",
    "battle_index": 1,
    "party": [ 1, 2, 3, 4 ]
  },
  {
    "floor_level": "12-1",
    "battle_index": 1,
    "party": [ 1, 2, 4, 5 ]
  },
  {
    "floor_level": "12-1",
    "battle_index": 1,
    "party": [ 1, 2, 4, 5]
  },
  {
    "floor_level": "12-2",
    "battle_index": 1,
    "party": [ 1, 2, 3, 4]
  },
  {
    "floor_level": "12-2",
    "battle_index": 1,
    "party": [ 1, 2, 4, 5]
  }
]

如果我查询floor_level的"12-1"，"12-2“，我希望看到这个输出：

[
  {
    "floor": "12-1",
    "parties": [
      { 
        "party": [ 1, 2, 3, 4 ],
        "count": 1
      },
      { 
        "party": [ 1, 2, 4, 5 ],
        "count": 2
      }
    ]
  },
  {
    "floor": "12-2",
    "parties": [
      { 
        "party": [ 1, 2, 3, 4 ],
        "count": 1
      },
      { 
        "party": [ 1, 2, 4, 5 ],
        "count": 1
      }
    ]
  }
]

尝试

我试过在这里引用资源：mongodb组值按多个字段划分

db.aggregate([
  { "$group": {
    "floor": { $concat: [$floor_level, $battle_index] },
  }},
  { "$lookup": {
    "from": "party",
    "pipeline": [
      { "$match": { 
        "$expr": { "$eq": [ "$party", "$$party"] }
      }},
      { "$group": {
        "party": "$party",
        "count": { "$sum": 1 }
      }},
      { "$sort": { "count": -1  } },
    ],
    "as": "parties"
  }}
])

但是，我发现用嵌套数组和对象的稍微复杂一些的结构来实现我的结果要困难得多。

Answer 1

形成更新的预期输出，希望这对您有所帮助。

db.collection.aggregate([
  {
    "$group": {
      "_id": { level: "$floor_level", party: "$party" },
      "count": { "$sum": 1 }
    }
  },
  {
    "$group": {
      "_id": "$_id.level",
      "parties": {
        "$push": {
          party: "$_id.party",
          count: "$count"
        }
      }
    }
  }
])

Working 蒙戈游乐场

假设还需要对battel索引进行分组，则可以使用

db.collection.aggregate([
  {
    "$group": {
      "_id": { level: "$floor_level", party: "$party", index:"$battle_index" },
      "count": { "$sum": 1 }
    }
  },
  {
    "$group": {
      "_id": { "level": "$_id.level", "index": "$_id.index" },    
      "parties": {
        "$push": {
          party: "$_id.party",
          count: "$count"
        }
      }
    }
  },
  {
    $addFields: {
      floor: { "$concat": [ "$_id.level", "-", { $toString: "$_id.index" } ] },
      _id: "$$REMOVE",
      index: "$$REMOVE"
    }
  }
])

Working 蒙戈游乐场