MongoDB lookup with pipeline for nested joins-CodePudding

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?

CodePudding user response：

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"}}}])