MongoDB.live, free & fully virtual. June 9th - 10th. Register Now MongoDB.live, free & fully virtual. June 9th - 10th. Register Now

Grouping Data between events

Hi there,

i’m new to NoSql and the nosql-queries.

I’m searching for a way to Group Data between 2 Eventtypes like login and logout.
I want to list all Logins and what happens until the User sends logout. Is there a way to do this Serverside?

How can i do this performant with MongoDB.

I’m using C# .net core 3.1 for developing.

Source:

[
{event: _id: '{5883E716-947B-4403-BF5D-B9C2BBED3177}', datetime: '2020-01-01T00:00:00', data:'{key:Username, Value:Username}', eventType: 'login'},
{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532A}', datetime: '2020-01-01T01:00:00', data:'{key:Name, Value:AnyFirstName}', eventType: 'add'},
{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532C}', datetime: '2020-01-01T03:00:00', data:'{key:Name, Value:AnyFirstName}', eventType: 'add'},
{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532C}', datetime: '2020-01-01T03:00:00', data:'{key:Name, Value:AnyFirstName}', eventType: 'edit'},
{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532C}', datetime: '2020-01-01T03:00:00', data:'{key:Name, Value:AnyFirstName}', eventType: 'add'},
{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532B}', datetime: '2020-01-01T03:10:00', data:'{key:Username, Value:Username}', eventType: 'logout'},
{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532D}', datetime: '2020-01-02T00:00:00', data:'{key:Username, Value:Username}', eventType: 'login'},
{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532A}', datetime: '2020-01-02T01:00:00', data:'{key:Name, Value:AnyFirstName}', eventType: 'edit'},
{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532C}', datetime: '2020-01-02T03:00:00', data:'{key:Name, Value:AnyFirstName}', eventType: 'add'},
{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532C}', datetime: '2020-01-02T03:00:00', data:'{key:Name, Value:AnyFirstName}', eventType: 'edit'},
{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532C}', datetime: '2020-01-02T03:00:00', data:'{key:Name, Value:AnyFirstName}', eventType: 'add'},
{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532B}', datetime: '2020-01-02T03:10:00', data:'{key:Username, Value:Username}', eventType: 'logout'}
]

so the Result should be something like this:

[
{
Start:2020-01-01T00:00:00,
End:2020-01-01T03:10:00,
Entries:{
	{event: _id: '{5883E716-947B-4403-BF5D-B9C2BBED3177}', datetime: '2020-01-01T00:00:00', data:'{key:Username, Value:Username}', eventType: 'login'},
	{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532A}', datetime: '2020-01-01T01:00:00', data:'{key:Name, Value:AnyFirstName}', eventType: 'add'},
	{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532C}', datetime: '2020-01-01T03:00:00', data:'{key:Name, Value:AnyFirstName}', eventType: 'add'},
	{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532C}', datetime: '2020-01-01T03:00:00', data:'{key:Name, Value:AnyFirstName}', eventType: 'edit'},
	{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532C}', datetime: '2020-01-01T03:00:00', data:'{key:Name, Value:AnyFirstName}', eventType: 'add'},
	{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532B}', datetime: '2020-01-01T03:10:00', data:'{key:Username, Value:Username}', eventType: 'logout'}
	}
},{
Start:2020-01-02T00:00:00,
End:2020-01-02T03:10:00,
Entries:{
	{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532D}', datetime: '2020-01-02T00:00:00', data:'{key:Username, Value:Username}', eventType: 'login'},
	{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532A}', datetime: '2020-01-02T01:00:00', data:'{key:Name, Value:AnyFirstName}', eventType: 'edit'},
	{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532C}', datetime: '2020-01-02T03:00:00', data:'{key:Name, Value:AnyFirstName}', eventType: 'add'},
	{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532C}', datetime: '2020-01-02T03:00:00', data:'{key:Name, Value:AnyFirstName}', eventType: 'edit'},
	{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532C}', datetime: '2020-01-02T03:00:00', data:'{key:Name, Value:AnyFirstName}', eventType: 'add'},
	{event: _id: '{4008D4D7-9786-4C5F-8924-F05E9CC1532B}', datetime: '2020-01-02T03:10:00', data:'{key:Username, Value:Username}', eventType: 'logout'}
	}
}
]

thank you so much
Thomas

This is working from the mongo shell.

Assuming the users collection has documents like this:

[
  { _id: 1, datetime: '2020-01-01T00:00:00', Username:'user-1', eventType: 'login'},
  { _id: 2, datetime: '2020-01-01T01:00:00', Username:'user-1', eventType: 'add'},
  ...

The following aggregation

db.users.aggregate( [
  { 
      $sort: { Username: 1, datetime: 1} 
  },
  { 
      $group: {
           _id: "$Username", 
           Start: { $first: "$datetime" }, 
           End: { $last: "$datetime" },
           Entries:  { $push: "$$ROOT" }
      } 
  },
  {
      $project: { _id: 0 }
  }
] )

produces a result like this:

{
        "Start" : "2020-01-01T00:00:00",
        "End" : "2020-01-01T03:10:00",
        "Entries" : [
                {
                        "_id" : 1,
                        "datetime" : "2020-01-01T00:00:00",
                        "Username" : "user-1",
                        "eventType" : "login"
                },
          ...
}, ...

Thank you for your Help.

I think this Solution will Group by Username.
In my case, i don’t have a Username for each Event.
Also a User kann have more than 1 Login and i want to the Duration foreach Login until a logout.

Is there a way to get Elements between Login and Logout when there are many Login,Logouts.

The Login,Logout example is only an Example for a simular Problem.
I have many Start Stop Entries, after a Start there are always some Entries and then a End, so i need to Group this Events from a Start to his End, then the next Set (Start to End).

If it is possible i would like to do this with Mongo and not in the Code.

Like:
Event Start
Event SelectArticle4711
Event 1
Event 2
Event 3
Event Stop
Event Start
Event SelectArticle4712
Event 1
Event 2
Event 3
Event 4
Event Stop

I think this Solution will Group by Username.

Correct.

In my case, i don’t have a Username for each Event.

So, what is the criteria by which to relate an event with a user or login? How is this key:Name, Value:AnyFirstName related with a user?

Also a User kann have more than 1 Login and i want to the Duration foreach Login until a logout.

So, there is no login session identifier for the user’s login (and the events are not identified by it)…

Is there a way to get Elements between Login and Logout when there are many Login,Logouts

It is related to the above previous two points.


The Login,Logout example is only an Example for a simular Problem.
I have many Start Stop Entries, after a Start there are always some Entries and then a End, so i need to Group this Events from a Start to his End, then the next Set (Start to End).

If it is possible i would like to do this with Mongo and not in the Code.

It is possible, but with appropriate input.