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.

turivishal · Accepted Answer · 2021-02-05 16:33:15Z

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

edited Feb 5, 2021 at 16:33

answered Feb 5, 2021 at 16:24

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

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

4 Comments

Stackedo Over a year ago

Thank you! They work like a charm. Just a last question: how about complexity? Could they be slow in case I have a lot of data?

turivishal Over a year ago

yes it would me slow because of lots of conversation, i would suggest to improve your schema and structure as per your actual project requirement. this kind of operations may cause performance issues. you can see which stage takes more process time using explain()

Stackedo Over a year ago

okay, thank you. A last last question: about the second one aggregate, if I limit to 10 users per time (the conversion) the request for a single group (not third like in the example), would this be the same slow?

turivishal Over a year ago

it depends on elements in array, you will get better idea using explain() function.

Collectives™ on Stack Overflow

MongoDB aggregate: count of elements in an object key

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related