I have the following data structure :

[
  {
    "_id": {
      "$oid": "6295d353fccb2f44c3868f9a"
    },
    "name": "test",
    "managerid": {
      "$oid": "6295d353fccb2f44c3868f99"
    },
    "teachers": [],
    "employees": [
      {
        "employeeid": {
          "$oid": "6295d353fccb2f44c3868f99"
        },
        "firstname": "jean",
        "lastname": "charles",
        "tag": 4595,
        "status": 1
      }
    ],
    "groups": [
      {
        "groupid": {
          "$oid": "6295fa2191ae50ba47e9d25c"
        },
        "groupname": "mongroupe",
        "employees": [],
        "permissions": []
      },
      {
        "groupid": {
          "$oid": "6295fa2191ae50ba47e9d25d"
        },
        "groupname": "mygroup",
        "employees": [],
        "permissions": []
      }
    ]
  }
]

I would like to add employee objects to one group. The objective is to get to something like this :

[
  {
    "_id": {
      "$oid": "6295d353fccb2f44c3868f9a"
    },
    "name": "test",
    "managerid": {
      "$oid": "6295d353fccb2f44c3868f99"
    },
    "teachers": [],
    "employees": [
      {
        "employeeid": {
          "$oid": "6295d353fccb2f44c3868f99"
        },
        "firstname": "jean",
        "lastname": "charles",
        "tag": 4595,
        "status": 1
      }
    ],
    "groups": [
      {
        "groupid": {
          "$oid": "6295fa2191ae50ba47e9d25c"
        },
        "groupname": "mongroupe",
        "employees": [
          {
            "employeeid": {
              "$oid": "6295d353fccb2f44c3868f99"
            },
            "firstname": "jean",
            "lastname": "charles"
          }
        ],
        "permissions": []
      },
      {
        "groupid": {
          "$oid": "6295fa2191ae50ba47e9d25d"
        },
        "groupname": "mygroup",
        "employees": [],
        "permissions": []
      }
    ]
  }
]

So that the employee is also embedded in the group object. This would be pretty easy with the following code (basing myself on this answer https://stackoverflow.com/a/65112446/16686765) :

db.collection.update({
  "_id": {
    "$oid": "6295d353fccb2f44c3868f9a",
  },
  "groups.groupid": {
    "$oid": "6295fa2191ae50ba47e9d25c"
  }
},
{
  "$set": {
    "groups.$.employees": {
      "$concatArrays": [
        "$groups.employees",
        {
          "$filter": {
            "input": [
              {
                "employeeid": {
                  "$oid": "6295d353fccb2f44c3868f99"
                }
              }
            ],
            "cond": {
              "$not": {
                "$in": [
                  "$$this.employeeid",
                  "$groups.$.employees.employeeid"
                ]
              }
            }
          }
        }
      ]
    }
  }
})

BUT the thing is aggregation pipelines can't work with positional operators field.$.otherfield, neither with arrayfilters.

As a consequence I can't select the right group ("groups.groupid": {"$oid": "6295fa2191ae50ba47e9d25c"} filter)

Anybody know what to do ? I'm pretty new to aggregation pipelines... Here's the related playground : https://mongoplayground.net/p/8Kgftb2oBpV

CodePudding user response：

You can do the followings with aggregation pipeline:

1. $match with your criteria
2. $unwind at groups level
3. $addFields and process the logic with $filter
4. $group to reform the unwinded result
5. $merge to perform the update back to the collection

db.collection.aggregate([
  {
    $match: {
      "_id": {
        "$oid": "6295d353fccb2f44c3868f9a"
      },
      "groups.groupid": {
        "$oid": "6295fa2191ae50ba47e9d25c"
      }
    }
  },
  {
    "$unwind": {
      path: "$groups",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    "$addFields": {
      "groups.employees": {
        "$cond": {
          "if": {
            $eq: [
              "$groups.groupid",
              ObjectId("6295fa2191ae50ba47e9d25c")
            ]
          },
          "then": {
            "$reduce": {
              "input": "$groups.employees",
              "initialValue": [
                {
                  "employeeid": {
                    "$oid": "6295d353fccb2f44c3868f99"
                  }
                }
              ],
              "in": {
                "$cond": {
                  "if": {
                    "$in": [
                      "$$this.employeeid",
                      "$$value.employeeid"
                    ]
                  },
                  "then": "$$value",
                  "else": {
                    "$concatArrays": [
                      "$$value",
                      [
                        "$$this"
                      ]
                    ]
                  }
                }
              }
            }
          },
          "else": "$groups.employees"
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      employees: {
        $first: "$employees"
      },
      groups: {
        $push: "$groups"
      },
      "managerid": {
        $first: "$managerid"
      },
      "name": {
        $first: "$name"
      },
      "teachers": {
        $first: "$teachers"
      }
    }
  },
  {
    "$merge": {
      "into": "collection",
      "on": "_id",
      "whenMatched": "replace"
    }
  }
])

Here is the Mongo Playground for your reference.