Joining two Collections with $lookup and nested Object

[Marcus_Baitz]

Hi, I have 2 collections that I would like to bring together using a field in the respective collection.

I created an example in Playground. Can one of you please help me. Thank you very much!

Playground

DB:

db={
  "assessmentNotice": [
    {
      "_id": {
        "$oid": "653d5269bddbd13af40c339a"
      },
      "referenceNumber": "220000007131",
      "status": "UNMAPPED"
    }
  ],
  "measurementNotice": [
    {
      "_id": {
        "$oid": "653d5268bddbd13af40c3396"
      },
      "data": {
        "referenceNumber": "220000003003"
      }
    },
    {
      "_id": {
        "$oid": "653d5269bddbd13af40c3399"
      },
      "data": {
        "referenceNumber": "220000007131"
      }
    }
  ]
}

Query:

db.assessmentNotice.aggregate([
  {
    $lookup: {
      from: "measurementNotice",
      let: {
        ref_number: "$data.referenceNumber"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$$ref_number",
                "$referenceNumber"
              ]
            }
          }
        }
      ],
      as: "lookup"
    }
  }
])

Result:

[
  {
    "_id": ObjectId("653d5269bddbd13af40c339a"),
    "lookup": [
      {
        "_id": ObjectId("653d5268bddbd13af40c3396"),
        "data": {
          "referenceNumber": "220000003003"
        }
      },
      {
        "_id": ObjectId("653d5269bddbd13af40c3399"),
        "data": {
          "referenceNumber": "220000007131"
        }
      }
    ],
    "referenceNumber": "220000007131",
    "status": "UNMAPPED"
  }
]

[steevej (Steeve Juneau)]

It looks like you simply inverted the fields names.

In your $let, you have access to the assessmentNotice fields, but you use $data.referenceNumber. The field data is in the measurementNotice documents. What would make sense with the sample documents would be

let: {
        ref_number: "$referenceNumber"
      },

and

$match: {
            $expr: {
              $eq: [
                "$$ref_number",
                "$data.referenceNumber"
              ]
            }
          }

But when your pipeline: $match is a simple equality like yours, it would be better to use

"localField" : "referenceNumber " , 
"foreignField" : "data.referenceNumber" ,

[Marcus_Baitz]

Hi Steeve Juneau,

I assumed that I had access to the measurement fields in the $lookup because of the “from”.

Thank you for your time.