Help re-grouping after unwind

[John_Grant]

I am implementing the Event Sourcing & CQRS patterns. I have a collection of documents with the events from users to manipulate models. I want to create a separate collection of the models for querying with the aggregation framework.

One challenge is that the model will have arrays within nested-documents that are stored under arrays themselves. The collection of events conveniently stores the user inputs in a similar shape as the intended model, which allows me to use $unwind for each level of array nesting. I was able to $group one level of nesting, and I assumed I simply needed to $group again but I’m unclear how to do it with the results I’m seeing in Compass.

// here is my collection of user events

[{
  "id": "event-1",
  "userId": "bob",
  "type": "CHARACTER_CREATE",
  "payload": {
    "AKAs": [
      {
        "id": "aka-1",
        "original": {
          "language": "en",
          "value": "Ross"
        },
        "season": [
          "1"
        ]
      }
    ],
    "id": "character-1"
  }
},{
  "id": "event-2",
  "userId": "alice",
  "type": "CHARACTER_ADD_AKAS",
  "payload": {
    "id": "character-1",
    "AKAs": [
      {
        "id": "aka-2",
        "original": {
          "language": "en",
          "value": "Ross Geller"
        },
        "season": [
          "2"
        ]
      }
    ]
  }
},{
  "id": "event-3",
  "userId": "bob",
  "type": "CHARACTER_ADD_AKA_TITLES",
  "payload": {
    "id": "character-1",
    "AKAs": [
      {
        "id": "aka-2",
        "season": [
          "3"
        ]
      }
    ]
  }
}]

// here is an example of the expected result after using the aggregation framework.
// combine payloads above to make this model

{
  "id": "character-1",
  "AKAs": [
    {
      "id": "aka-2",
      "season": [
        "1"
      ],
      "original": {"language": "en", "value": "Ross Geller"}
    },
    {
      "id": "aka-2",
      "season": [
        "2",
        "3"
      ],
      "original": {"language": "en", "value": "Ross"}
    }
  ]
}

// here is my attempt to compose the aggregation - it feels 95% done.

[
{$match: {
  // overkill i know, but will be useful later
  type: {$in: [
    "CHARACTER_CREATE",
    "CHARACTER_ADD_AKAS",
    "CHARACTER_ADD_AKA_TITLES",
    "CHARACTER_ADD_AKA_LOCS"
  ]},
  // the real filter
  id: "character-1"
}},
// i only want to look at payload in the output for now
{$project: {
  _id: 0,
  payload: 1
}},
// one level of arrays
{$unwind: {
  path: "$payload.AKAs",
  preserveNullAndEmptyArrays: true
}},
// the next level of arrays
{$unwind: {
  path: "$payload.AKAs.season",
  preserveNullAndEmptyArrays: true
}}, {$unwind: {
  path: "$payload.AKAs.localization",
  preserveNullAndEmptyArrays: true
}},
// now the fun part, this outputs 2 documents which look exactly like what I intended for the AKAs.
{$group: {
  _id: {entityId: "$payload.id", akaId: "$payload.AKAs.id"},
  "original": {$mergeObjects: "$payload.AKAs.original"},
  "season": {
    $push: "$payload.AKAs.season"
  }
}},
// fail: I dont know how to join the 2 documents output from above into array elements on one final output document.
{$group: {
  _id: {id: "$_id.entityId"},
  // ERROR: "$" is not valid. Also, how do I get the AKAs.id in each array element?
  AKAs: {$push: "$"}
}}]

[John_Grant]

This is solved.

I did not realize you can use an expression in $push that specifies multiple keys.

The last stage needed to be:

$group: {
  _id: {id: "$_id.entityId"},
  // the fix
  AKAs: { $push: {
    "id": "$_id.akaId",
    "season": "$season",
    "original": "$original"
  }}
}

[John_Grant]

Actually I have a follow up question.

If the first user event, CHARACTER_CREATE, contained a series attribute that happens to be an array, e.g. payload.series : ["Friends"], I am confused how to send that attribute through the pipeline. Any aggregation I try results in an array of arrays.

The result after aggregation should look like:

{
  "id": "character-1",
  "series": ["Friends"],
  "AKAs": [..as shown above.]
}