Let's say I have a sample object as below

[
  {
    "status": "ACTIVE",
    "person": [
      {
        "name": "Jim",
        "age": "2",
        "qualification": [
          {
            "type": "education",
            "degree": {
              "name": "bachelor",
              "year": "2022"
            }
          },
          {
            "type": "certification",
            "degree": {
              "name": "aws",
              "year": "2021"
            }
          }
        ]
      }
    ]
  }
]

Now, I need to add a field "score" only when the qualification.type == bachelor

This is the query I tried but could not get the proper result. Not sure what mistake I am doing. Any help is highly appreciated. Thank you in advance.

db.collection.aggregate([
  {
    $addFields: {
      "person.qualification.score": {
        $reduce: {
          input: "$person.qualification",
          initialValue: "",
          in: {
            $cond: [
              {
                "$eq": [
                  "$$this.type",
                  "bachelor"
                ]
              },
              "80",
              "$$REMOVE"
            ]
          }
        }
      }
    }
  }
])

CodePudding user response：

1. $set - Set person array field.

   1.1. $map - Iterate person array and return a new array.

   1.1.1. $mergeObjects - Merge current iterate (person) document and the document with qualification.

   1.1.1.1. $map - Iterate the qualification array and return a new array.

   1.1.1.1.1. $cond - Match type of current iterate qualification document. If true, merge current iterate qualification document and the document with score field via $mergeObjects. Else remain the existing document.

db.collection.aggregate([
  {
    $set: {
      person: {
        $map: {
          input: "$person",
          as: "p",
          in: {
            $mergeObjects: [
              "$$p",
              {
                qualification: {
                  $map: {
                    input: "$$p.qualification",
                    in: {
                      $cond: {
                        if: {
                          $eq: [
                            "$$this.type",
                            "education"
                          ]
                        },
                        then: {
                          $mergeObjects: [
                            "$$this",
                            {
                              score: "80"
                            }
                          ]
                        },
                        else: "$$this"
                      }
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Sample Mongo Playground