I would like to put the note with the corresponding item, I am close... but just can't figure out to do the final "matching" to get the notes with the right item

For example

the "Water" item should have "Water note 1" but not "Fire note 1"

The "Fire" item should have "Fire note 1" but not "Water note 1"

Here are my data sets:

Inspections

{
  "_id": {"$oid": "635c37d00017b0adec605f01" },
  "REF_InspectionItems": [
    {"$oid": "635c37d30017b0adec605f1d" },
    {"$oid": "635c37d60017b0adec605f34" }
  ]
}

InspectionItems

[
  {
    "_id": {"$oid": "635c37d30017b0adec605f1d"},
    "name": "Water",
    "REF_InspectionItemNotes": [
      {"$oid": "635c37d40017b0adec605f26" },
      {"$oid": "635c37d50017b0adec605f2c" }
    ]
  },
  {
    "_id": {"$oid": "635c37d60017b0adec605f34"},
    "name": "Fire",
    "REF_InspectionItemNotes": [
      {"$oid": "635c37d70017b0adec605f3d" }
    ]
  }
]

InspectionItemNotes

[
  {
    "_id": {"$oid": "635c37d40017b0adec605f26"},
    "text": "Water note 1"
  },
  {
    "_id": {"$oid": "635c37d50017b0adec605f2c"},
    "text": "Water note 2"
  },
  {
    "_id": {"$oid": "635c37d70017b0adec605f3d"},
    "text": "Fire note 1"
  }
]

Pipeline

Here is what I have tried

[
  {
    '$lookup': {
      'from': 'inspectionitems', 
      'localField': 'REF_InspectionItems', 
      'foreignField': '_id', 
      'as': 'inspectionItemsLookup'
    }
  }, {
    '$lookup': {
      'from': 'inspectionitemnotes', 
      'localField': 'inspectionItemsLookup.REF_InspectionItemNotes', 
      'foreignField': '_id', 
      'as': 'inspectionItemNotesLookup'
    }
  }, {
    '$project': {
      'items': {
        '$map': {
          'input': '$inspectionItemsLookup', 
          'as': 'temp', 
          'in': {
            '$mergeObjects': [
              '$$temp', {
                'notes': {
                  '$map': {
                    'input': '$inspectionItemNotesLookup', 
                    'as': 'temp2', 
                    'in': {
                      '$mergeObjects': [
                        '$$temp2', {
                          'thisIsNotShowingUp': {
                            '$first': {
                              '$filter': {
                                'input': '$inspectionItemNotesLookup', 
                                'cond': {
                                  '$eq': [
                                    '$$temp2.REF_InspectionItemNotes', '$$this._id'
                                  ]
                                }
                              }
                            }
                          }
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
]

this is what I expect:

ANSWER

as Yong Shun pointed out... it was a 'set' not a 'project' and I had one too many 'map' calls than what was necessary

{
    $set: {
      items: {
        $map: {
          input: "$inspectionItemsLookup",
          as: "item",
          in: {
            $mergeObjects: [
              "$$item",
              {
                notes: {
                  $filter: {
                    input: "$inspectionItemNotesLookup",
                    cond: {
                      $in: [
                        "$$this._id",
                        "$$item.REF_InspectionItemNotes"
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }

CodePudding user response：

You are close to the answer.

The notes array which would be appended with item object by filtering from the inspectionItemNotesLookup array that the document's id is in the REF_InspectionItemNotes array.

db.inspection.aggregate([
  {
    "$lookup": {
      "from": "inspectionitems",
      "localField": "REF_InspectionItems",
      "foreignField": "_id",
      "as": "inspectionItemsLookup"
    }
  },
  {
    "$lookup": {
      "from": "inspectionitemnotes",
      "localField": "inspectionItemsLookup.REF_InspectionItemNotes",
      "foreignField": "_id",
      "as": "inspectionItemNotesLookup"
    }
  },
  {
    $set: {
      items: {
        $map: {
          input: "$inspectionItemsLookup",
          as: "item",
          in: {
            $mergeObjects: [
              "$$item",
              {
                notes: {
                  $filter: {
                    input: "$inspectionItemNotesLookup",
                    cond: {
                      $in: [
                        "$$this._id",
                        "$$item.REF_InspectionItemNotes"
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $unset: [
      "REF_InspectionItems",
      "inspectionItemsLookup",
      "inspectionItemNotesLookup",
      "itemNotes"
    ]
  }
])

Demo @ Mongo Playground