Merge $lookup result to existing array

Question

I'm new to mongo and I need your help.

I have collection studijneProgramy. This is sample document:

{
    "_id" : "dGFY",
    "garranti" : [
        {
            "typ" : {
                "sk" : "garant",
                "en" : "Chairman of study board"
            },
            "id" : "1025769"
        },
        {
            "typ" : {
                "sk" : "predseda odborovej komisie",
                "en" : "Chairman of study board"
            },
            "id" : "1025769"
        }
    ]
}

Next I have collection osoby.
Example document:

{
    "_id" : "1025769",
    "plneMeno" : "prof. RNDr. Peter Moczo, DrSc.",
    "priezvisko" : "Moczo",
    "meno" : "Peter",
    "jeGarantProgramu" : "dGFY/x"
}

What I need is to add documets from osoby to corresponding document in array garranti (where studijneProgramy.garanti.id == osoby._id). So this is my desired result:

{
    "_id" : "dGFY",
    "garranti" : [
        {
            "typ" : {
                "sk" : "garant",
                "en" : "Chairman of study board"
            },
            "id" : "1025769"
            "garant":{
                "_id" : "1025769",
                "plneMeno" : "prof. RNDr. Peter Moczo, DrSc.",
                "priezvisko" : "Moczo",
                "meno" : "Peter",
                "jeGarantProgramu" : "dGFY/x"
            }
        },
        {
            "typ" : {
                "sk" : "predseda odborovej komisie",
                "en" : "Chairman of study board"
            },
            "id" : "1025769"
            "garant":{
                "_id" : "1025769",
                "plneMeno" : "prof. RNDr. Peter Moczo, DrSc.",
                "priezvisko" : "Moczo",
                "meno" : "Peter",
                "jeGarantProgramu" : "dGFY/x"
            }
        }
    ]
}

I tried this aggregation but it replaced content of garranti.

db.studijneProgramy.aggregate([
{
    $lookup:
    {
        from:"osoby", 
        localField:"garranti.id",
        foreignField:"_id", 
        as:"garranti.garant"
    }
 }
]
).pretty()

Any help would be greatly appreciated!

Answer 1

MongoDB $lookup will not "update" elements in an existing array with matches from the "lookup" collection. It will only output an "array" of the matches to the criteria given, be that matched against an "existing array" of values as you have or a singular value.

In order to "marry up" the entries with a "server" $lookup operation you have to proceed instead with one of the following options in order to return in the form you want.

$unwind The Array First

The simplest form is to simply change the structure of the documents so that each array member from the source is it's own document first, before you actually attempt to "marry up" the correlated information:

db.studijneProgramy.aggregate([
  { "$unwind": "$garranti" },
  { "$lookup": {
    "from": "osoby",
    "as": "garranti.garrant",
    "localField": "garranti.id",
    "foreignField": "_id"
  }},
  { "$unwind": "$garranti.garrant" },
  { "$group": {
    "_id": "$_id",
    "garranti": { "$push": "$garranti" }
  }}
])

Since the original array material are now singular documents, then each one only receives the "array" of matches from the joined collection. This would $unwind again and finally use $group in order to $push to the final array form with "joined" entries.

Correlate the "arrays"

A bit fancier in versions that support it are to use the functions of $indexOfArray and $arrayElemAt in order to "match up" the output array of $lookup to the existing array entries in the document:

db.studijneProgramy.aggregate([
  { "$lookup": {
    "from": "osoby",
    "as": "related",
    "localField": "garranti.id",
    "foreignField": "_id"
  }},
  { "$project": {
    "garranti": {
      "$map": {
        "input": "$garranti",
        "in": {
          "typ": "$$this.typ",
          "id": "$$this.id",
          "garrant": {
            "$arrayElemAt": [
              "$related",
              { "$indexOfArray": [ "$related._id", "$$this.id" ] }
            ]
          }
        }
      }
    }
  }}
])

So the lookup returns the "array of matches" ( related ) and you "lookup" the matching entries of these and transpose them into the original document array via $map. Of course this requires an additional $project stage or similar in order to reshape the document result since you cannot "target" each element of the existing array in $lookup output as mentioned earlier.

This is actually the direct correlation on the "server" of what some libraries such as "mongoose" do for "join emulation on the client". Effectively the "foreign" entries are "mapped" onto the existing array.

Sub-pipeline Processing

A bit fancier and long-winded is another alternative using "sub-pipeline" processing of an Uncorrelated subquery available from MongoDB 3.6 an upwards. Here we basically do the manipulation in the "sub-pipeline" of $lookup instead of processing in subsequent aggregation stages:

db.studijneProgramy.aggregate([
  { "$lookup": {
    "from": "osoby",
    "as": "garranti",
    "let": { "garranti": "$garranti" },
    "pipeline": [
      { "$match": {
        "$expr": { "$in": [ "$_id", "$$garranti.id" ] } 
      }},
      { "$addFields": {
        "docs": {
          "$filter": {
            "input": "$$garranti",
            "cond": {
              "$eq": [ "$$this.id", "$_id" ]
            }
          }
        }
      }},
      { "$unwind": "$docs" },
      { "$replaceRoot": {
        "newRoot": {
          "$mergeObjects": [
            "$docs",
            { "garrant": {
              "$arrayToObject": {
                "$filter": { 
                  "input": { "$objectToArray": "$$ROOT" },
                  "cond": { "$ne": [ "$$this.k", "docs"] }
                }
              }
            }}
          ]
        }
      }}
    ]
  }}
])

This sort of turns the operation "on it's head" and effectively places the "matching array elements" from the "source document" into each matched foreign element as an array.

The processing then effectively uses $unwind on the filtered source list and then merges the content from the foreign collection so it now appears that the $lookup "output array" is actually the data from the "local array" now "merged" with the "foreign content".

Really it's just a fancier invocation of the same $map process above, but doing the "correlating" of entries before the results are merged with the original parent document overwriting the original array property.

---

There is I think a JIRA around for this somewhere, but I sort of have the feeling that "works as designed" is marked on all such reports, so it is unlikely to change from what it presently does.

So the misconception you had was the "join" would "merge" with the array entries "automatically". It does not.

If if you want to actually "merge array output", then the approaches above are the "server" approach to do so.