MongoDB - Add fields to the object in an array from another object array based on matching condition-CodePudding

I'm trying to write an aggregation in MongoDB which would result as shown below. Please suggest to me how to build MongoDB Aggregation in order to achieve my output.

Collection (input): I would like to add a key qf_id to the wkObj.cFS object based on matching condition cf_id.

Input Document:

[
  {
    "dataObj": {
      "dataObj_id": "test_1",
      "cFS": {
        "data": [
          {
            "cf_id": "123",
            "name": "cf_1"
          },
          {
            "cf_id": "456",
            "name": "cf_2"
          }
        ]
      }
    },
    "cfCoreData": [
      {
        "cf_id": "123",
        "qf_id": "QF_1989"
      },
      {
        "cf_id": "456",
        "qf_id": "QF_6090"
      }
    ]
  }
]

Intended Result:

[
  {
    "cfCoreData": [
      {
        "cf_id": "123",
        "qf_id": "QF_1989"
      },
      {
        "cf_id": "456",
        "qf_id": "QF_6090"
      }
    ],
    "dataObj": {
    "dataObj_id": "test_1",
      "cFS": {
        "data": [
          {
            "cf_id": "123",
            "name": "cf_1",
            "qf_id": "QF_1989"
          },
          {
            "cf_id": "456",
            "name": "cf_2",
            "qf_id": "QF_6090"
          }
        ]
      }
    }
  }
]

When I try the below query, it's adding as an array.

db.collection.aggregate({
  $addFields: {
    "dataObj.cFS.data.qf_id": "$cfCoreData.qf_id"
  }
})

Please suggest an optimal way of writing a MongoDB query to achieve my result.

CodePudding user response：

$set - Set dataObj.cFS.data field.

1.1. $map - Iterate each element in dataObj.cFS.data and return a new array.

1.1.1. $mergeObjects - Merge the current iterate object (data) with the result from 1.1.1.1.

1.1.1.1. $first - Get the first filtered document from the result 1.1.1.1.1.

1.1.1.1.1. $filter - Filter the document(s) from the cfCoreData array by matching the cf_id.

db.collection.aggregate([
  {
    $set: {
      "dataObj.cFS.data": {
        $map: {
          input: "$dataObj.cFS.data",
          as: "data",
          in: {
            $mergeObjects: [
              "$$data",
              {
                $first: {
                  $filter: {
                    input: "$cfCoreData",
                    cond: {
                      $eq: [
                        "$$data.cf_id",
                        "$$this.cf_id"
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Demo @ Mongo Playground