I want to update all documents that match a field and move this element (and I want all properties of this element kept).

Dataset :

[{
    "name": "Guillaume",
    "childrens": [{
        "name": "Robert",
        "degree": "License"
    }
    ],
    "students": [
    {
        "name": "Hélène",
        "degree": "License"
    }]
},
{
    "name": "Mathilde",
    "childrens": [ {
        "name": "Lucie",
        "degree": "License"
    }],
    "students": [
    {
        "name": "Michel",
        "degree": "License"
    }]
}]

For example, I would like to update "childrens.degree" = Master to all childrens that match name "Robert" and move this child to "students". Expected result :

[{
    "name": "Guillaume",
    "childrens": [],
    "students": [
    {
        "name": "Hélène",
        "degree": "License"
    },
    {
        "name": "Robert",
        "degree": "Master"
    }]
},
{
    "name": "Mathilde",
    "childrens": [
    {
        "name": "Lucie",
        "degree": "License"
    }],
    "students": [
    {
        "name": "Michel",
        "degree": "License"
    }]
}]

I update all documents that match Robert, but I failed to move child Robert in students

db.person.updateMany(
{"childrens.name": "Robert" },
{
        $set: {
            "childrens.$[child].degree": "Master"
        }
},
{ arrayFilters: [ {"child.name": "Robert"} ] }
)

What I tried :

db.person.updateMany(
{"childrens.name": "Robert" },
{
        $set: {
            "childrens.$[child].degree": "Master",
            $push: {
                "students": "childrens.$[child]"
            }
        }
},
{ arrayFilters: [ {"child.name": "Robert"} ] }
)

CodePudding user response：

You want to be using pipelined updates for this, Now we can just filter out the children array from all of the "roberts", while using concatArrays to append them to the students array (while also changing their degree), like so:

db.collection.updateMany(
{ "childrens.name": "Robert" },
[
  {
    $set: {
      childrens: {
        $filter: {
          input: {
            $ifNull: [
              "$childrens",
              []
            ]
          },
          cond: {
            $ne: [
              "$$this.name",
              "Robert"
            ]
          }
        }
      },
      students: {
        $concatArrays: [
          {
            $ifNull: [
              "$students",
              []
            ]
          },
          {
            $map: {
              input: {
                $filter: {
                  input: {
                    $ifNull: [
                      "$childrens",
                      []
                    ]
                  },
                  cond: {
                    $eq: [
                      "$$this.name",
                      "Robert"
                    ]
                  }
                }
              },
              in: {
                $mergeObjects: [
                  "$$this",
                  {
                    degree: "Master"
                  }
                ]
              }
            }
          }
        ]
      }
    }
  }
])

Mongo Playground

CodePudding user response：

db.person.updateMany(
{"childrens.name": "Robert"},
{
    $push: {
        "students": {"name": "Robert", "degree": "Masters"}
    },
    $pull: {
        "childrens": {"name": "Robert"}
    }
}
);

Playground