CODEWITHSUNDEEP
mongodbaggregation-framework
Gugic
Gugic

Reputation: 109

Mongo db $group dynamic expression

I have a set of logs with a timestamp and needs to group that logs by some non-existent 'virtual session'.

New grouped session begins if there is half of hour between last log in previous session and first log in this.

For example we have following set of data:

[
{
    id: "b4f0d0d7-495b-48db-95bf-d5ac0c8c9e9b"
    time: 1461872894322
    timestamp: "Apr 28, 2016 7:48:14 PM",
},
{
    id: "bf55ca2f-b544-406c-bed6-766a1204683d"
    time: 1461872937941
    timestamp: "Apr 28, 2016 7:48:57 PM"
},
{
    id: "7f2ab420-0434-46f8-9444-6e2ffa73aea8"
    time: 1461873088155
    timestamp: "Apr 28, 2016 7:51:28 PM"
},
{
    id: "dd31124c-0375-454a-acca-c239465a2b22"
    time: 1461839257257
    timestamp: "Apr 28, 2016 10:27:37 AM"
},
{
    id: "a4370974-bfea-408f-aa69-973961e9f058"
    time: 1461839281324
    timestamp: "Apr 28, 2016 10:28:01 AM"
}
]

It should be grouped in two virtual sessions. As a result of grouping i can get min and max time for each group in mongo aggregate $group, but how to write the correct expression?

Expected answer is something like

[
{min: 1461872894322, max: 1461873088155},
{min: 1461839257257, max: 1461839281324}
]

Upvotes: 3

Views: 423

Answers (1)

profesor79
profesor79

Reputation: 9473

Unfortunately there is no way to do it by mongo query as there is no handle for previous row (like CTE common table expressions).

To solve this problem you need to process data client side (or using javascript in mongo console - like a SP from sql world) and iterate over all documents checking for time gap and adding a grouping indicator to collection. Then you will be able to group by added grouping indicator.

Was thinking of suing $let as it can access external variable - but this is RO access so we cannot relay on that.

Have a fun! Any comments welcome.

Upvotes: 1

Related Questions