MongoDB lookup with pipeline for nested joins

Question

i have two collections on which i am performing lookup to get combined data as a result.

Company :

{
  "_id": "638760ea-d109-49fd-8447-b52fe39227a3",
  "company": "test",
  "phones": [
    {
      "iso": "in",
      "number": "54666",
      "label": "CF_61a8c36b3368b0b21dbfbe3d"
    },
    {
      "iso": "hu",
      "number": "54433",
      "label": "CF_61a8c37d3368b0b21dbfbe3e"
    }
  ]
}

ContactLables:

{
  "_id": "096b0446-1099-49f4-87fc-21e583581780",
  "values": [
    {
      "id": "0a5c3f36-a06d-4f34-a1a9-9bbef7370940",
      "code": "CF_61a8c36b3368b0b21dbfbe3d",
      "value": "Personal"
    },
    {
      "id": "693574f9-cb30-48b1-9394-7673c9e71f33",
      "code": "CF_61a8c37d3368b0b21dbfbe3e",
      "value": "Home"
    }
  ]
}

I want output as

{
  "_id": "638760ea-d109-49fd-8447-b52fe39227a3",
  "company": "test",
  "phones": [
    {
      "iso": "in",
      "number": "54666",
      "label": "CF_61a8c36b3368b0b21dbfbe3d",
      "value": "Personal"
    },
    {
      "iso": "hu",
      "number": "54433",
      "label": "CF_61a8c37d3368b0b21dbfbe3e",
      "value": "Home"
    }
  ]
}

Here is the lookup that i am applying to get the results

{
  from: 'ContactLables',
  let:'ContactLablesCode':'$values.code',
  pipeline: [
      { "$match": {
        "$expr": { "$eq": [ "$phones.label", "$$ContactLablesCode" ] }
      }}
    ],
  as: 'phoneLables'
}

Above query dos not gives any result for me, any suggestion on this?

Answer 1

Query1

• unwind phones
• lookup with pipeline but version MongoDB 5 we have localfield/foreighfield + pipeline
• localfield= the phones.label and foreign=array with the codes
• if match => the array has the label we want but we don't know which member
• unwind and match to keep only the value for our phone.label for example "Home"
• now from every phone we have its value from the other collection
• fix structure with set/unset (1 set and "$$REMOVE" would work also)
• finally group back and push the phones to the _id owner

*to make it fast you need multikey index on ContactLables.value and MongoDB 5 (query here is for MongoDB 5 to be faster and idenx use)

Test code here

Company.aggregate(
[{"$unwind":"$phones"},
 {"$lookup":
  {"from":"ContactLables",
   "localField":"phones.label",
   "foreignField":"values.code",
   "pipeline":
   [{"$unwind":"$values"},
    {"$match":{"$expr":{"$eq":["$values.code", "$$label"]}}},
    {"$project":{"_id":0, "value":"$values.value"}}],
   "as":"results",
   "let":{"label":"$phones.label"}}},
 {"$set":{"phones.value":{"$first":"$results.value"}}},
 {"$unset":["results"]},
 {"$group":
  {"_id":"$_id",
   "company":{"$first":"$company"},
   "phones":{"$push":"$phones"}}}])

Query2

• same as above, but inside the pipeline reduce is used instead of unwind and match
• reduce to find the member that does the match, and keep its value for example "Home"

Test code here

Company.aggregate(
[{"$unwind":"$phones"},
 {"$lookup":
  {"from":"ContactLables",
   "localField":"phones.label",
   "foreignField":"values.code",
   "pipeline":
   [{"$project":
     {"_id":0,
      "value":
      {"$reduce":
       {"input":"$values",
        "initialValue":null,
        "in":
        {"$let":
         {"vars":{"v":"$$value"},
          "in":
          {"$cond":
           [{"$eq":["$$this.code", "$$label"]}, "$$this.value",
            "$$v"]}}}}}}}],
   "as":"results",
   "let":{"label":"$phones.label"}}},
 {"$set":{"phones.value":{"$first":"$results.value"}}},
 {"$unset":["results"]},
 {"$group":
  {"_id":"$_id",
   "company":{"$first":"$company"},
   "phones":{"$push":"$phones"}}}])