How to calculate max 'win' streak over sequential documents

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!

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

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

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

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.

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!

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.

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