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过滤来自 ObjectIds在 party大批。我尝试在 party 处汇总记录包含完全相同的 4 ObjectId s(已排序)。我想按 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表示具体 party的编号找到相应的 floor .每个 floor会有多个 party在一个数组中，每个 party是 4 ObjectId 的独特组合s。
示例
假设我们在这里有一个简化版本的集合:

[
  {
    "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 s [ "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 group values by multiple fields

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"
        }
      }
    }
  }
])

工作Mongo playground
假设您还需要对 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"
    }
  }
])

工作Mongo playground