How to $group data to exactly same shape after unwinding three arrays

We have three nested arrays:

  1. principalCredits with 2 objects
  2. credits with 2 objects each
  3. awardNominations.edges with variable totals from 0 to 3

The task is to add a field to the third array of objects awardNominations.edges based on a lookup from eventsCollection.

Here’s the data I have (simplified, can copy and paste into MongoDB Compass):

[{
  "principalCredits": [
    {
      "category": {
        "id": "director",
        "text": "Directors"
      },
      "totalCredits": 2,
      "credits": [
        {
          "name": {
            "id": "nm11813828",
            "nameText": {
              "text": "Pippa Ehrlich"
            },
            "awardNominations": {
              "total": 2,
              "edges": [
                {
                  "node": {
                    "id": "an1393007",
                    "isWinner": true,
                    "award": {
                      "id": "an1393007",
                      "year": 2020,
                      "text": "Green Warsaw Award",
                      "event": {
                        "id": "ev0003786",
                        "text": "Millennium Docs Against Gravity"
                      },
                      "category": {
                        "text": null
                      }
                    }
                  }
                },
                {
                  "node": {
                    "id": "an1428940",
                    "isWinner": false,
                    "award": {
                      "id": "an1428940",
                      "year": 2021,
                      "text": "IDA Award",
                      "event": {
                        "id": "ev0000351",
                        "text": "International Documentary Association"
                      },
                      "category": {
                        "text": "Best Writing"
                      }
                    }
                  }
                },
              ]
            }
          },
          "category": {
            "id": "director",
            "text": "Director"
          }
        },
        {
          "name": {
            "id": "nm1624755",
            "nameText": {
              "text": "James Reed"
            },
            "awardNominations": {
              "total": 3,
              "edges": [
                {
                  "node": {
                    "id": "an0694012",
                    "isWinner": true,
                    "award": {
                      "id": "an0694012",
                      "year": 2015,
                      "text": "Best of Festival",
                      "event": {
                        "id": "ev0001486",
                        "text": "Jackson Wild Media Awards"
                      },
                      "category": {
                        "text": "Best of Festival"
                      }
                    }
                  }
                },
                {
                  "node": {
                    "id": "an0975779",
                    "isWinner": true,
                    "award": {
                      "id": "an0975779",
                      "year": 2017,
                      "text": "RTS West Television Award",
                      "event": {
                        "id": "ev0000571",
                        "text": "Royal Television Society, UK"
                      },
                      "category": {
                        "text": "Documentary"
                      }
                    }
                  }
                },
                {
                  "node": {
                    "id": "an0975781",
                    "isWinner": true,
                    "award": {
                      "id": "an0975781",
                      "year": 2015,
                      "text": "Grand Teton Prize",
                      "event": {
                        "id": "ev0001356",
                        "text": "Jackson Hole Film Festival"
                      },
                      "category": {
                        "text": "Best in Festival"
                      }
                    }
                  }
                }
              ]
            }
          },
          "category": {
            "id": "director",
            "text": "Director"
          }
        }
      ]
    },
    {
      "category": {
        "id": "writer",
        "text": "Writers"
      },
      "totalCredits": 2,
      "credits": [
        {
          "name": {
            "id": "nm11813828",
            "nameText": {
              "text": "Pippa Ehrlich"
            },
            "awardNominations": {
              "total": 2,
              "edges": [
                {
                  "node": {
                    "id": "an1393007",
                    "isWinner": true,
                    "award": {
                      "id": "an1393007",
                      "year": 2020,
                      "text": "Green Warsaw Award",
                      "event": {
                        "id": "ev0003786",
                        "text": "Millennium Docs Against Gravity"
                      },
                      "category": {
                        "text": null
                      }
                    }
                  }
                },
                {
                  "node": {
                    "id": "an1428940",
                    "isWinner": false,
                    "award": {
                      "id": "an1428940",
                      "year": 2021,
                      "text": "IDA Award",
                      "event": {
                        "id": "ev0000351",
                        "text": "International Documentary Association"
                      },
                      "category": {
                        "text": "Best Writing"
                      }
                    }
                  }
                }
              ]
            }
          },
          "category": {
            "id": "writer",
            "text": "Writer"
          },
        },
        {
          "name": {
            "id": "nm1624755",
            "nameText": {
              "text": "James Reed"
            },
            "awardNominations": {
              "total": 0,
              "edges": []
            }
          },
          "category": {
            "id": "writer",
            "text": "Writer"
          },
        }
      ]
    }
  ]
}]

An example scored award should look like this:

{
  "id": "an0975781",
  "isWinner": true,
  "award": { ... },
  "score": 1.2
}

Once all the manipulation is done, the data needs to be in exactly the same shape as it was initially and with no null values. So in the case of the last array awardsNominations.edges it should be [] as it was, and not { node: { score: null }} or anything else.

To achieve this I have created an aggregation pipeline:

[
  {
    '$unwind': {
      'path': '$principalCredits', 
      'preserveNullAndEmptyArrays': true
    }
  }, {
    '$unwind': {
      'path': '$principalCredits.credits', 
      'preserveNullAndEmptyArrays': true
    }
  }, {
    '$unwind': {
      'path': '$principalCredits.credits.name.awardNominations.edges', 
      'preserveNullAndEmptyArrays': true
    }
  }, {
    '$lookup': {
      'from': 'eventsCollection', 
      'localField': 'principalCredits.credits.name.awardNominations.edges.node.award.event.id', 
      'foreignField': 'id',
      'as': 'matchingEvent'
    }
  }, {
    '$unwind': {
      'path': '$matchingEvent', 
      'preserveNullAndEmptyArrays': true
    }
  }, {
    '$addFields': {
      'principalCredits.credits.name.awardNominations.edges.node.score': {
        '$multiply': [
          '$matchingEvent.importance', {
            '$cond': {
              'if': '$principalCredits.credits.name.awardNominations.edges.node.isWinner', 
              'then': 1.5, 
              'else': 1.2
            }
          }
        ]
      }
    }
  }
]

The above pipeline assigns the score to each award. However, the null values are still there and I have absolutely no idea how to group it back together. I have tried to group with:

{
  '$group': {
    '_id': '$id', 
    'titleDoc': {
      '$first': '$$ROOT'
    }, 
    'allPrincipalCredits': {
      '$push': '$principalCredits'
    }
  }
}

To keep the root and then somehow sort all the records back into shape but could not get back to the orginal object structure.

Any help in putting it all together will be much appriciated!

I’m fairly good with simple aggregations, but this seems to be too much for me currently and would love to learn how to $group things back properly.

I’ve tried and put together all the knowledge I have so far form different sources and similar answers but can’t seem to get it to work.

Lookup collection eventsCollection contains objects like this:

{  
  "_id": { "$oid": "62c57125d6943d92f83f6fff" },  
  "id": "ev0030197",  
  "text": "#AmLatino Film Festival",  
  "importance": 1
}

Unwind and group to get the same shape is an anti-pattern. See

First you do not need to $unwind before $lookup. It is smart enough to lookup each values from the array. What is nice is that duplicate looked up values are not duplicated in the resulting array. The array is different from the original array. You may use $map on the original array if absolutely necessary to have the $lookup values within the original array.

1 Like