MongoDB aggregate match by array of ObjectIds

Question

COLLECTION

I have a collection abyssBattles with the following data:

[
  {
    "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"
    ]
  }
]

I'm mainly filtering by floor_level and by ObjectIds in the party array. I'm try to aggregate records where party contains the exact same 4 ObjectIds (already sorted). I want to group these by the floor_level and battle_index they belong, and get sum the count for the party.

DESIRED RESULT

The result I'm trying to achieve would look like so:

[
  { 
    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
     },
     ...
   ]
  }
]

Where count represents the number of the specific party found for the respective floor. Each floor would have multiple party in an array, each party being a unique combination of 4 ObjectIds.

EXAMPLE

Let's say we have a simplified version of the collection here:

[
  {
    "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]
  }
]

If I query for floor_levels [ "12-1", "12-2" ] I would expect to see this output:

[
  {
    "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
      }
    ]
  }
]

ATTEMPT

I have tried referring to the resource here: 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"
  }}
])

But I found it harder to achieve my results with the slightly more complex structure of nested arrays and objects.

Can you also share sample input from both the collections and expected output based on the sample data along with logic you want to apply? — hhharsha36, Jun 19 '21 at 17:33

varman · Accepted Answer · 2021-06-19T18:52:02.460

Form the updated expected output, hope this would help you

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 Mongo playground

Suppose you need the battel index also to be grouped, you can use

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 Mongo playground

Worked beautifully, thank you so much! Your use of `$group` was also much easier to follow than other methods I've seen. — bouhm, Jun 20 '21 at 21:24

MongoDB aggregate match by array of ObjectIds

1 Answers1