Mongodb Aggregation count array/set size

Question

Here's my problem:

Model:

{ application: "abc", date: Time.now, status: "1" user_id: [ id1, id2, id4] }

{ application: "abc", date: Time.yesterday, status: "1", user_id: [ id1, id3, id5] }

{ application: "abc", date: Time.yesterday-1, status: "1", user_id: [ id1, id3, id5] }

I need to count the unique number of user_ids in a period of time.

Expected result:

{ application: "abc", status: "1", unique_id_count: 5 }

I'm currently using the aggregation framework and counting the ids outside mongodb.

{ $match: { application: "abc" } }, { $unwind: "$users" }, { $group: { _id: { status: "$status"}, users: { $addToSet: "$users" } } }

My arrays of users ids are very large, so I have to iterate the dates or I'll get the maximum document limit (16mb).

I could also $group by

{ year: { $year: "$date" }, month: { $month: "$date" }, day: { $dayOfMonth: "$date" }

but I also get the document size limitation.

Is it possible to count the set size in mongodb?

thanks

Do you have more than 16mb ids per user or do you have more than 16mbs of data for all of the records? If the latter condition exits, you can try flushing the result to an ouput collection. — cubbuk
– cubbuk, Commented Jan 28, 2013 at 18:13
The users array/set size is bigger than a thousand, and user ids are similar to object_ids (50b9d949816e6e37060005c2). The previous version was using map/reduce and an output collection. It was terribly slower. Counting in memory is faster than writing an output collection. — user2019059
– user2019059, Commented Jan 28, 2013 at 18:17
And how is the performance when you make a table scan and retrieve only application and userId fields. Of course counting in memory is faster but you have limitations with mongo, as far as I know if the output doesn't fit into memory flushing to disk or making a table scan is your only option. — cubbuk
– cubbuk, Commented Jan 28, 2013 at 18:30
The performance is acceptable. I just wish for a way to count an array size and not return the entire content. — user2019059
– user2019059, Commented Jan 28, 2013 at 18:40

Ray Toal · Accepted Answer · 2013-08-13 21:05:32Z

25

The following will return number of uniqueUsers per application. This will apply an group operation to a result of a group operation by using pipeline feature of mongodb.

{ $match: { application: "abc" } }, 
{ $unwind: "$users" }, 
{ $group: { _id: "$status", users: { $addToSet: "$users" } } }, 
{ $unwind:"$users" }, 
{ $group : {_id : "$_id", count : {$sum : 1} } }

Hopefully this will be done in an easier way in the following releases of mongo by a command which gives the size of an array under a projection. {$project: {id: "$_id", count: {$size: "$uniqueUsers"}}} https://jira.mongodb.org/browse/SERVER-4899

Cheers

edited Aug 13, 2013 at 21:05

Ray Toal

88.7k20 gold badges186 silver badges245 bronze badges

answered Jan 28, 2013 at 19:16

cubbuk

7,9284 gold badges39 silver badges64 bronze badges

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

2 Comments

Nevena Over a year ago

This is added in version 2.5.3 (currently development release)

Matt Wilson Over a year ago

Your example {$project: {id: "$_id", count: {$size: "$uniqueUsers"}}} worked for me in version 2.6. Thanks!

mjhm · Accepted Answer · 2013-01-28 20:38:32Z

2

Sorry I'm a little late to the party. Simply grouping on the 'user_id' and counting the result with a trivial group works just fine and doesn't run into doc size limits.

[
    {$match: {application: 'abc', date: {$gte: startDate, $lte: endDate}}},
    {$unwind: '$user_id'},
    {$group: {_id: '$user_id'}},
    {$group: {_id: 'singleton', count: {$sum: 1}}}
];

answered Jan 28, 2013 at 20:38

mjhm

16.8k10 gold badges47 silver badges56 bronze badges

1 Comment

Asya Kamsky Over a year ago

It also doesn't satisfy the question "I need to count the unique number of user_ids in a period of time" OP already knows how to do it for each period of time.

xiansweety · Accepted Answer · 2019-04-24 09:44:48Z

1

Use $size to get the size of set.

[
    {
        $match: {"application": "abc"}
    },
    {
        $unwind: "$user_id"
    },
    {
        $group: {
            "_id": "$status",
            "application": "$application",
            "unique_user_id": {$addToSet: "$user_id"}
        }
    },
    {
        $project:{
            "_id": "$_id",
            "application": "$application",
            "count": {$size: "$unique_user_id"}
        }
    }
]

answered Apr 24, 2019 at 9:44

xiansweety

1431 silver badge4 bronze badges

Collectives™ on Stack Overflow

Mongodb Aggregation count array/set size

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related