How to calculate max 'win' streak over sequential documents

Working with Data
,
1

Lets say I have a collection of documents like:
[
{ createdAt: 1688383980100, win: true }
{ createdAt: 1688383980200, win: false }
{ createdAt: 1688383980300, win: true }
{ createdAt: 1688383980400, win: true }
{ createdAt: 1688383980500, win: false }
]

How can I get the maximum win streak?
I managed to get it via $group and $accumulator with custom JS functions.
But Digital Ocean does not allow JS on their mongo DB servers.

Then I spent almost a day trying many pipeline alternatives, no success.
Any tips or pointers are super welcome! Thanks in advance!

2

I posted the question here on SO too. WIll update the forum if we get a solution there.

3

Now I’m not proud of this…but…

db.getCollection("Streak").aggregate([
{
    $project:{
        outVal:{
            $cond:{
                if:{$eq:['$win', true]},
                then:'1',
                else:'0'
            }
        }
    }
},
{
    $group:{
        _id:null,
        allRuns:{$push:'$outVal'}
    }
},
{
    $project:{
        totalString:{
            $reduce:{
                input:'$allRuns',
                initialValue:'',
                in:{$concat:['$$value', '$$this']}
            }
        }
    }
},
{
    $project:{
        splitItems:{
            $split:[
                '$totalString',
                '0'
            ]
        }
    }
},
{
    $unwind:'$splitItems'
},
{
    $addFields:{
        length:{
            $strLenCP:'$splitItems'
        }
    }
},
{
    $sort:{
        length:-1
    }
},
{
    $limit:1
}
])

This has some rather glaring issues with scalability, but I guess you could run it on a period of data at a time…

Edit
Change the condition to this instead:

    if:'$win',

It annoys me to compare a boolean to true…

2 Likes
4

Thanks @John_Sewell! The code works! I also got a reply on SO, will play with the suggestions see what I can come up with and report back here later.

1 Like
5

Yes, I saw the reply on SO, was going to have a play with that reply later as not used the window interval operator previously.

6

Checking performance over a collection of about 200,000 records, my solution with the string splitting takes about 6s to complete, and the SO reply takes about 1s so it’s a LOT more performant.
They both arrive at the same answer, which is at least something for my solution!

7

Thanks a lot for the help and tests @John_Sewell!
For the of delivery of the service I was working last week,
I implemented that calc on Node.JS.

But I’ll circle back to it and move the calc to Mongo side,
I’ll update the thread here when that is done.

8

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.