MongoDB - Move some fields from the array into another array

Question

I have this simplified MongoDB document and would like to change something because there is quite a lot of redundant data. This field "activeUsersLookup" is the result of aggregation which returns data I'd like to put inside the first users array.

First id: "_id": "80b1565a-faf4-4e68-9bd6-8344060e8d3a" matches id from activeUsersLookup the same story is with user IDs.

[{
  "_id": "80b1565a-faf4-4e68-9bd6-8344060e8d3a",
  "users": [
    {
      "_id": "eaa946da-2708-443e-ab4c-b6db357050ca",
      "lastactive": {
        "$date": {
          "$numberLong": "1637922656000"
        }
      }
    },
    {
      "_id": "4972ba13-6f4e-4943-be07-15802e22e0dd",
      "lastactive": {
        "$date": {
          "$numberLong": "1653286066000"
        }
      }
    },
    {
      "_id": "6c4a62ce-c6c6-430f-a0cd-d348ec77dbb2",
      "lastactive": {
        "$date": {
          "$numberLong": "1558623982000"
        }
      }
    }
  ],
  "activeUsersLookup": [
    {
      "_id": "80b1565a-faf4-4e68-9bd6-8344060e8d3a",
      "users": [
        {
          "_id": "eaa946da-2708-443e-ab4c-b6db357050ca",
          "activities": 2
        },
        {
          "_id": "6c4a62ce-c6c6-430f-a0cd-d348ec77dbb2",
          "activities": 1
        }
      ],
      "sumOfActivities": 3
    }
  ]
}]

So more or less the final document should look like this:

[{
  "_id": "80b1565a-faf4-4e68-9bd6-8344060e8d3a",
  "users": [
    {
      "_id": "eaa946da-2708-443e-ab4c-b6db357050ca",
      "lastactive": {
        "$date": {
          "$numberLong": "1637922656000"
        }
      },
      "activities": 2
    },
    {
      "_id": "4972ba13-6f4e-4943-be07-15802e22e0dd",
      "lastactive": {
        "$date": {
          "$numberLong": "1653286066000"
        }
      },
      "activities": 0
    },
    {
      "_id": "6c4a62ce-c6c6-430f-a0cd-d348ec77dbb2",
      "lastactive": {
        "$date": {
          "$numberLong": "1558623982000"
        }
      },
      "activities": 1
    },
    "sumOfActivities": 3
  ]
}]

I've tried with:

{
    $addFields: {
      'licenses.activities': '$activeUsersLookup.users.activities'
    }
}

But this gives me an empty array so I must be doing something wrong. The next stage would be to sum all those activities as sumOfActivities and the last stage would be unset activeUsersLookup.

What magic tricks must I do to have the needed result? :)

Yong Shun · Accepted Answer · 2022-07-09 02:48:04Z

I don't think the expected result you posted for the "sumOfActivities": 3 in the users array is valid.

Assume that you are trying to achieve the result as below:

[{
  "_id": "80b1565a-faf4-4e68-9bd6-8344060e8d3a",
  "users": [...],
  "sumOfActivities": 3
}]

The query is a bit long:

$set - Set activeUsersLookup field as object.

1.1. $first - Get the first document from 1.2.

1.2. $filter - Filter document(s) from activeUsersLookup by matching _id for the document in activeUsersLookup with _id (root document).
$set

2.1. - Set users array.

2.1.1. $map - Iterate the documents in users array and return a new array.

2.1.2. $mergeObjects - Merge current documents with the documents with activities field.

2.1.3. $ifNull - Set activities as 0 if no result returned from 2.1.4.

2.1.4. $getField - Get the activities field from the result 2.1.5.

2.1.5. $first - Get the first document from the result 2.1.6.

2.1.6. $filter - Filter the activeUsersLookup.users documents by matching _id for the document (users array) with _id for the current document.

2.2. Set sumOfActivities field.
$unset - Remove activeUsersLookup field.

db.collection.aggregate([
  {
    $set: {
      activeUsersLookup: {
        $first: {
          $filter: {
            input: "$activeUsersLookup",
            cond: {
              $eq: [
                "$$this._id",
                "$_id"
              ]
            }
          }
        }
      }
    }
  },
  {
    $set: {
      users: {
        $map: {
          input: "$users",
          as: "user",
          in: {
            $mergeObjects: [
              "$$user",
              {
                activities: {
                  "$ifNull": [
                    {
                      "$getField": {
                        "field": "activities",
                        "input": {
                          $first: {
                            $filter: {
                              input: "$activeUsersLookup.users",
                              cond: {
                                $eq: [
                                  "$$this._id",
                                  "$$user._id"
                                ]
                              }
                            }
                          }
                        }
                      }
                    },
                    0
                  ]
                }
              }
            ]
          }
        }
      },
      sumOfActivities: "$activeUsersLookup.sumOfActivities"
    }
  },
  {
    $unset: "activeUsersLookup"
  }
])

Sample Mongo Playground

Works, thank you very much! You should get Nobel prize in category "mongodb queries" ;)

Collectives™ on Stack Overflow

MongoDB - Move some fields from the array into another array

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