Arrayfilter equivalent in mongodb update aggregation pipeline

Question

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

ray · Accepted Answer · 2022-06-01T11:09:30.807

You can do the followings with aggregation pipeline:

$match with your criteria
$unwind at groups level
$addFields and process the logic with $filter
$group to reform the unwinded result
$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.

Thanks but your example only works if there is only one group in the array. When you add more groups, you realize the employees get added to all of them which is not what i want. Look : https://mongoplayground.net/p/nJ3srypDCkT — raphael.oester, Jun 01 '22 at 07:38
@rrrrr sorry my bad. updated the answer to perform the groupid checking with a `$cond` — ray, Jun 01 '22 at 11:09
Yes perfect it works ! Aggregation pipelines seem very tricky tho... Do you know any good resource to learn how to use them ? :) — raphael.oester, Jun 01 '22 at 12:43
@rrrrr Stackoverflow is generally not a suitable place to seek recommendations for books, tools, software libraries etc. But typically when you google out the behaviour you want to achieve, you will be redirected to some StackOverflow answers that might help — ray, Jun 01 '22 at 12:49

Arrayfilter equivalent in mongodb update aggregation pipeline

1 Answers1