How can I merge array fields by matching ids in mongodb?

Question

Let's say I have two fields member and userData in the same collection like below.

{
    member: [
    { _id: ObjectId("60ff03a462256b70fdb979dc"), role: "admin" },
    { _id: ObjectId("60ff03a462256b70fdb97932"), role: "member" },
    { _id: ObjectId("60ff03a462256b70fdb97995"), role: "member" },
  ],

  userData: [
    {
      _id: ObjectId("60ff03a462256b70fdb979dc"),
      name: "Kevin",
      email: "[email protected]",
      ...
    },
    {
      _id: ObjectId("60ff03a462256b70fdb97932"),
      name: "Andrew"
      email: "[email protected]",
      ...
    },
    {
      _id: ObjectId("60ff03a462256b70fdb97995"),
      name: "Jessica",
      email: "[email protected]",
      ...
    },
    ....
  ]
}

How can I merge the two fields(essentially inserting member into userData) by their _id to populate this result?

userWithRole: [
    {
      _id: ObjectId("60ff03a462256b70fdb979dc"),
      name: "Kevin",
      email: "[email protected]",
      role: "admin"
      ... // all other user data
    }

Takis · Accepted Answer · 2021-10-04 00:18:00Z

7

Query

map each member of userData
find his role by filtering themember array
merge objects to add the role

Test code here

*query assumes that your sample data are 1 document with 2 array fields.

*The bellow is aggregation if you want update you can use the same code by doing an update with pipeline like update({},[...pipelineBellow..])

db.collection.aggregate([
  {
    "$project": {
      "userWithRole": {
        "$map": {
          "input": "$userData",
          "in": {
            "$let": {
              "vars": {
                "m": {
                  "$arrayElemAt": [
                    {
                      "$filter": {
                        "input": "$member",
                        "cond": {
                          "$eq": [
                            "$$mb._id",
                            "$$this._id"
                          ]
                        },
                        "as": "mb"
                      }
                    },
                    0
                  ]
                }
              },
              "in": {
                "$mergeObjects": [
                  "$$this",
                  {
                    "role": "$$m.role"
                  }
                ]
              }
            }
          }
        }
      }
    }
  }
])

edited Oct 4, 2021 at 0:18

answered Oct 4, 2021 at 0:12

Takis

8,7552 gold badges17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bg_k Over a year ago

Thanks a lot for your help! It worked🙏🙏🙏

Collectives™ on Stack Overflow

How can I merge array fields by matching ids in mongodb?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related