Group embedded array of documents only

[Leodennis (Leo)]

Hi,

I want to group an array of documents that is nested in another document without it affecting the parent document.

If I have an array of ids I already know how to do it using the internal pipeline of $lookup:

Database:

db={
  "users": [
    {
      "name": "David",
      "messages": [
        1,
        2
      ]
    },
    {
      "name": "Mia",
      "messages": [
        3,
        4,
        5
      ]
    }
  ],
  "messages": [
    {
      "_id": 1,
      "text": "hello",
      "type": "PERSONAL"
    },
    {
      "_id": 2,
      "text": "test",
      "type": "DIRECT"
    },
    {
      "_id": 3,
      "text": "hello worl",
      "type": "DIRECT"
    },
    {
      "_id": 4,
      "text": ":-)",
      "type": "PERSONAL"
    },
    {
      "_id": 5,
      "text": "hi there",
      "type": "DIRECT"
    }
  ]
}

Aggregation

db.users.aggregate([
  {
    "$lookup": {
      "from": "messages",
      "localField": "messages",
      "foreignField": "_id",
      "as": "messages",
      "pipeline": [
        {
          "$group": {
            "_id": "$type",
            "count": {
              "$sum": 1
            }
          }
        }
      ]
    }
  }
])

Result:

[
  {
    "_id": ObjectId("5a934e000102030405000005"),
    "messages": [
      {
        "_id": "PERSONAL",
        "count": 1
      },
      {
        "_id": "DIRECT",
        "count": 1
      }
    ],
    "name": "David"
  },
  {
    "_id": ObjectId("5a934e000102030405000006"),
    "messages": [
      {
        "_id": "DIRECT",
        "count": 2
      },
      {
        "_id": "PERSONAL",
        "count": 1
      }
    ],
    "name": "Mia"
  }
]

Playground

Now I want to archive the same but with an embedded document array:

db={
  "users": [
    {
      "name": "David",
      "messages": [
        {
          "text": "hello",
          "type": "PERSONAL"
        },
        {
          "text": "test",
          "type": "DIRECT"
        }
      ]
    },
    {
      "name": "Mia",
      "messages": [
        {
          "text": "hello worl",
          "type": "DIRECT"
        },
        {
          "text": ":-)",
          "type": "PERSONAL"
        },
        {
          "text": "hi there",
          "type": "DIRECT"
        }
      ]
    }
  ]
}

I cant find out how to do this, I know I can filter an embedded array using $addField and $fitler but not how I can group just the embedded array.

Please note that this is just a simple example my real data structure looks different and the user actually decides what to group for and might user other grouping functions like min, sum etc. but I just wanted to know a general way of archieving the same thing as when I use the lookup.

[Leodennis (Leo)]

I actually got an answer for this problem on stackoverflow so I wanted to share it.

All credit goes to aneroid on stackoverflow, this is not my solution:

1. Unwind messages
2. Group by the _id (presumably userID) and message type; and set countType with $count.
3. Then re-group by _id only and use the first doc (since it’s the same for non-message fields)

• Push each {type: ..., count: countType} into a messages array.
• Note that the documents are back to being one per user/_id

4. Set the doc.messages to the array messages which was pushed in the previous step.
5. Replace the root with the new doc which has all the correct info
6. (Optionally, sort on _id if you need it.)

db.users.aggregate([
  { $unwind: "$messages" },
  {
    $group: {
      _id: {
        _id: "$_id",
        type: "$messages.type"
      },
      countType: { $count: {} },
      doc: { $first: "$ROOT" }
    }
  },
  {
    $group: {
      _id: "$_id._id",
      doc: { $first: "$doc" },
      messages: {
        $push: {
          // since you want message type as `_id`
          _id: "$_id.type",
          count: "$countType"
        }
      }
    }
  },
  { $set: { "doc.messages": "$messages" } },
  { $replaceWith: "$doc" }
])

Mongo Playground

Group embedded array of documents only