CODEWITHSUNDEEP
c#mongodbaggregation-frameworkmongodb-.net-driver
Ori Refael
Ori Refael

Reputation: 3018

Group data by dates

Using C# MongoDb driver.

I have a Login collection which stores the last logins to the system.

I would like to group them into 2 groups: the last 24 hours and the last hour.

The object looks like that:

public sealed class Login
{
    [BsonId]
    public ObjectId UserId;
    public DateTime LastLogin;
}

Each user, as you may deduct, has only 1 row.

The expected result would look something like :

{
"Id" : "24Hours", "Count" : <some number>,
"Id" : "LastHour", "Count" : <some other number>
}

I have no experience with Aggregation and all of the example Ive seen on Wiki were all on grouping common fields, here I have data manipulation so I dont have the tools.

It would be nice if I'll be able to use AggregateAsync instead of Aggregate.

Upvotes: 0

Views: 477

Answers (2)

shA.t
shA.t

Reputation: 16968

I can use projection and gtoup in c# like this:

var yesterday = DateTime.Now.AddDays(-1);
var lastHour = DateTime.Now.AddHours(-1);

var projection = new BsonDocument
{
    {
        "Last24Hours",
        new BsonDocument("$cond",
            new BsonArray {new BsonDocument("$gte", new BsonArray {"$LastLogin", yesterday}), 1, 0})
    },
    {
        "LastHour",
        new BsonDocument("$cond",
            new BsonArray {new BsonDocument("$gte", new BsonArray {"$LastLogin", lastHour}), 1, 0})
    }
};

var groupBy = new BsonDocument
{
    {"_id", BsonNull.Value},
    {"CountLast24Hours", new BsonDocument("$sum", "$Last24Hours")},
    {"CountLastHour", new BsonDocument("$sum", "$LastHour")}
};

And get result by using a pipeline like this:

var pipeline = PipelineDefinition<Login, BsonDocument>.Create(
    new BsonDocument("$project", projection),
    new BsonDocument("$group", groupBy), 
    );
var result = col.Aggregate(pipeline).SingleOrDefault();

var countLast24Hours = result["CountLast24Hours"].AsInt32;
var countLastHour = result["CountLastHour"].AsInt32;

Upvotes: 1

Maksim Simkin
Maksim Simkin

Reputation: 9679

I have managed to do this with Aggregation framework. What i couldn't do is to convert bool flag, if login was within 24 hours to name, i did it with extra Select.

First we get all logins within last 24 hours, than we group results depending on if they were in last hour or nor (true/false), after that we get whole result as enumerable and perfome fine tuning on name, i have described before.

var result =
    collection.Aggregate()
       .Match(x => x.LastLogin >= DateTime.Now.AddDays(-1) && x.LastLogin <= DateTime.Now)
       .Group(r => r.LastLogin <= DateTime.Now.AddHours(-1), r =>
                       new { WithinLastHour = r.Key ,  Count = r.Count()})
       .ToEnumerable()
       .Select(x=>
                new {Name = x.WithinLastHour ? "Within last hour":"Within last 24 hours", 
                     x.Count})
       .ToList();

Sure, you could do AggregateAsync if you want.

Upvotes: 1

Related Questions