Lookup & populate objects in array

Working with Data
5

Ok, Took me a bit but here it is. As I said, your choice of words, especially “tried everything” confuses us about your level of understanding. So let me explain it so to improve you on what you might have missed on $lookup.

  • “from” other documents, “match” its “foreignField” to “localField” of this document. (or use a $match in pipeline) instead
  • define variables by "let"ting them be the values of local fields before going into the pipeline.
  • think of the pipeline as a fully disconnected query. it is just a query that is run on “tenants” collection with the exception of variables we defined before entering the pipeline.
    • all “$$” variables are special. either defined by us elsewhere (above step, for example), or MongoDB defined them. “$$ROOT” is the document currently under inspection down the pipeline.
  • put the result back into local collection “as” having a new name, or “replace” existing one with the same name.

And the tricky part is this:

  • We need values separately from the array since they are objects with fields we need later (role). so we need an “$unwin” first.
  • “$lookup” will give back an array even when it has 1 item inside, so need to:
    • “$unwind” it again.
    • then “$group” the resulting documents.
[
  {
    "$unwind": {
      "path": "$associatedTenants"
    }
  },
  {
    "$lookup": {
      "from": "tenant",
      "foreignField": "_id",
      "localField": "associatedTenants.tenantId",
      "let": {
        "tId": "$associatedTenants.tenantId",
        "tRole": "$associatedTenants.role"
      },
      "pipeline": [
        {
          "$project": {
            "_id": 0,
            "tenantId": "$_id",
            "role": "$$tRole",
            "tenant": "$$ROOT"
          }
        }
      ],
      "as": "associatedTenants"
    }
  },
  {
    "$unwind": {
      "path": "$associatedTenants"
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "associatedTenants": {
        "$push": "$associatedTenants"
      }
    }
  }
]

PS: a solution to a similar problem seems to exists here: mongodb - $lookup on ObjectId's in an array - Stack Overflow

2 Likes