Merge the original array of objects into the "as" field after a $lookup

Question

I have a hero collection where each hero document looks like the following:

{
    _id:'the-name-of-the-hero',
    name: 'Name of Hero',
    (...), //other properties to this hero

    relations: [
        {
            hero: 'the-id-of-another-hero',
            relationType: 'trust'
        },
        {
            hero: 'yet-another-id-of-another-hero',
            relationType: 'hate'
        }
    ]
}

The relations.hero points to an _id of another hero. I needed to grab some more information of the related heroes, therefore I used aggregate $lookup to match each against the "hero" collection, to grab it's name (and other data, but project simplified for the question). Here the currently working query, docummented:

let aggregate = db.collection('hero').aggregate([
    // grabbing an specific hero
    { $match: { _id } },
    //populate relations
    {
        $lookup: {
            from: 'hero',
            let: { letId: '$relations.hero' }, //create a local variable for the pipeline to use
            // localField: "relations.hero", //this would bring entire hero data, which is unnecessary
            // foreignField: "_id",  //this would bring entire hero data, which is unnecessary
            pipeline: [
                //match each $relations.hero (as "$$letId") in collection hero's (as "from") $_id
                { $match: { $expr: { $in: ['$_id', '$$letId'] } } },
                //grab only the _id and name of the matched heroes
                { $project: { name: 1, _id: 1 } },
                //sort by name
                { $sort:{ name: 1 } }
            ],
            //replace the current relations with the new relations
            as: 'relations',
        },
    }
]).toArray(someCallbackHere);

In short, $lookup on hero collection using a pipeline that match each of relations.hero and bring back only the _id and name (which has the real name to be printed on UI) and replace current relations with this new relations, generating the document as:

{
    _id:'the-name-of-the-hero',
    name: 'Name of Hero',
    (...), //other properties to this hero
    relations: [
        {
            _id: 'the-id-of-another-hero',
            name: 'The Real Name of Another Hero',
        },
        {
            _id: 'yet-another-id-of-another-hero',
            name: 'Yet Another Real Name of Another Hero',
        }
    ]
}

The question:

What can I add on the pipeline to make it merge the matched heroes with the original relations, in order to not only have the projected _id and name, but also the original relationType? That is, have the following result:

{
    _id:'the-name-of-the-hero',
    name: 'Name of Hero',
    (...), //other properties to this hero
    relations: [
        {
            _id: 'the-id-of-another-hero',
            name: 'The Real Name of Another Hero',
            relationType: 'trust' //<= kept from the original relations
        },
        {
            _id: 'yet-another-id-of-another-hero',
            name: 'Yet Another Real Name of Another Hero',
            relationType: 'hate' //<= kept from the original relations
        }
    ]
}

I tried exporting as: 'relationsFull' and then tried to $push with $mergeObjects as part of a next step into the aggregation but no luck. I tried to do the same as a pipeline step (instead of a new aggregate step) but always end up relations as empty array..

How would I write a new aggregation step to merge old relations objects with the new looked-up relations?

Note: Consider MongoDB 3.6 or later (that is, $unwind array is not needed, at least for the $lookup). I'm querying using Node.js driver, if that info matters.

Answer 1

You can use below aggregation

db.collection("hero").aggregate([
  { "$match": { _id } },
  { "$unwind": "$relations" },
  { "$lookup": {
    "from": "hero",
    "let": { "letId": "$relations.hero" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$_id", "$$letId"] } } },
      { "$project": { "name": 1 } }
    ],
    "as": "relation"
  }},
  { "$unwind": "$relation" },
  { "$addFields": { "relations.name": "$relation.name" }},
  { "$group": {
    "_id": "$_id",
    "relations": { "$push": "$relations" },
    "name": { "$first": "$name" },
    "rarity": { "$first": "$rarity" },
    "classType": { "$first": "$classType" }
  }}
])

Or alternate you can use this as well

db.collection("hero").aggregate([
  { "$match": { _id } },
  { "$lookup": {
    "from": "hero",
    "let": { "letId": "$relations.hero" },
    "pipeline": [
      { "$match": { "$expr": { "$in": ["$_id", "$$letId"] } } },
      { "$project": { "name": 1 } }
    ],
    "as": "lookupRelations"
  }},
  { "$addFields": {
    "relations": {
      "$map": { 
        "input": "$relations",
        "as": "rel",
        "in": {
          "$mergeObjects": [
            "$$rel",
            { "name": { "$arrayElemAt": ["$lookupRelations.name", { "$indexOfArray": ["$lookupRelations._id", "$$rel._id"] }] }}
          ]
        }
      }
    }
  }}
])

Answer 2

Well, I think we should use different name for the as field.From there, we can use the following expression the the $addFields stage.

{
   "$addFields": {
      "relations": {
         "$reduce": {
            "input": {
               "$reduce": {
                  "input": {
                     "$zip": {
                        "inputs": [
                           "$relations",
                           "$relheros"
                        ]
                     }
                  },
                  "initialValue": [

                  ],
                  "in": {
                     "$concatArrays": [
                        "$$value",
                        "$$this"
                     ]
                  }
               }
            },
            "initialValue": {

            },
            "in": {
               "$mergeObjects": [
                  "$$value",
                  "$$this"
               ]
            }
         }
      }
   }
}

Note that the relheros here is the as field.

We really should not $unwind and $group here, before $unwind is cheap but $group is expensive.