aggregation framework - MongoDB aggregate: count of elements in an object key

Question

I have a MongoDB document like this:

[
  {
    "_id": {
      "$oid": "5ff09030cd55d6d9f378d460"
    },
    "username": "a value",
    "userid": "123456",
    "last_access_ts": 1612426253,
    "last_access": "2021-2-4 9:10:53",
    "anotherid": 12345678910,
    "verified_date": "2021-1-2 16:24:32",
    "verified_ts": 1609601072,
    "group_users": {
      "-1001159747589": [
        {
          "anotherid": 12345678910,
          "userid": "123456"
        }
      ],
      "-1001143137644": [
        {
          "anotherid": 12345678910,
          "userid": "123456"
        }
      ],
      "-1001368608972": [
        {
          "anotherid": 12345678910,
          "userid": "123456"
        }
      ]
    },
    "registered_access": "2021-1-2 16:24:42",
  }
]

I've two questions.

First one: I need to count the elements inside each group_users[key] object, and I'm stuck with this aggregate:

db.collection.aggregate([
  {
    $match: {
      username: "a value"
    }
  },
  {
    $project: {
      _id: 1,
      userid: 1,
      "groups": {
        "$objectToArray": "$group_users"
      }
    }
  },
  {
    $unwind: "$groups",
  },
])

This aggregate gives me this result:

[
  {
    "_id": ObjectId("5ff09030cd55d6d9f378d460"),
    "groups": {
      "k": "-1001449720492",
      "v": [
        {
          "anotherid": 12345678910,
          "userid": "123456"
        }
      ]
    },
    "userid": "123456"
  },
  {
    "_id": ObjectId("5ff09030cd55d6d9f378d460"),
    "groups": {
      "k": "-1001159747589",
      "v": [
        {
          "anotherid": 12345678910,
          "userid": "123456"
        }
      ]
    },
    "userid": "123456"
  },
  {
    "_id": ObjectId("5ff09030cd55d6d9f378d460"),
    "groups": {
      "k": "-1001143137644",
      "v": [
        {
          "anotherid": 12345678910,
          "userid": "123456"
        }
      ]
    },
    "userid": "123456"
  }
]

How can I count each single groups[v] and then re-group the data? I would like to have a result like:

{
    ... some user data
    "groups": {
        "group_key": "count",
        "second_group_key": "count",
        "third_group_key": "count"
    }

}

Is it possible with aggregate or I need to loop in the code?

My second question is always about the group_users. Is possible to have, recursively, the user data inside a group_users object?

I mean, every object inside group_users is an array of users; from this array can I have the user data (maybe with $graphLookup?) using the userid field or the anotherid field?

As a result from this second aggregate I would like to have something like this:

{
    ... some user data
    "groups": {
        "group_key": [{"userid": userid, "username": username}],
        "second_group_key": [{"userid": userid, "username": username}],
        "third_group_key": [{"userid": userid, "username": username}]
    }
}

Obviously I can limit this "recursion" to 10 elements per time.

Thanks for any advice.

question from:https://stackoverflow.com/questions/66062748/mongodb-aggregate-count-of-elements-in-an-object-key

Answer 1

• $objectToArray convert group_users object to array
• $let to Bind variable group_arr for use in the specified expression, and returns the result of the expression.
• $map to iterate loop of bind variable group_arr, get size of total element of v and return k and v,
• $arrayToObject convert returned array from $map to object

db.collection.aggregate([
  { $match: { username: "a value" } },
  {
    $project: {
      _id: 1,
      userid: 1,
      groups: {
        $let: {
          vars: { group_arr: { $objectToArray: "$group_users" } },
          in: {
            $arrayToObject: {
              $map: {
                input: "$$group_arr",
                in: {
                  k: "$$this.k",
                  v: { $size: "$$this.v" }
                }
              }
            }
          }
        }
      }
    }
  }
])

Playground

---

Second question,

• $unwind deconstruct groups array
• $lookup with pipeline, match anotherid $in condition and return required fields
• $group by _id and reconstruct groups array
• $arrayToObject convert groups array to object

db.collection.aggregate([
  { $match: { username: "a value" } },
  {
    $project: {
      _id: 1,
      userid: 1,
      groups: { $objectToArray: "$group_users" }
    }
  },
  { $unwind: "$groups" },
  {
    $lookup: {
      from: "collection",
      let: { anotherid: "$groups.v.anotherid" },
      pipeline: [
        { $match: { $expr: { $in: ["$anotherid", "$$anotherid"] } } },
        {
          $project: {
            _id: 0,
            userid: 1,
            username: 1
          }
        }
      ],
      as: "groups.v"
    }
  },
  {
    $group: {
      _id: "$_id",
      groups: { $push: "$groups" },
      userid: { $first: "$userid" }
    }
  },
  { $addFields: { groups: { $arrayToObject: "$groups" } } }
])

Playground