[SERVER-8065] Add a way to determine a document's rank after a $sort Created: 03/Jan/13  Updated: 06/Dec/22  Resolved: 01/Jun/21

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Sam Halliday Assignee: Backlog - Query Optimization
Resolution: Duplicate Votes: 8
Labels: expression
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
depends on SERVER-56572 Allow ranking-style window functions ... Backlog
Duplicate
duplicates SERVER-56572 Allow ranking-style window functions ... Backlog
Assigned Teams:
Query Optimization
Participants:

 Description   

It would be useful to be able to add a documents position in a sort to the document. For example, there could be an additional option to the $sort stage:

{$sort: {
    order: {a: 1, b: 1},
    includeSortIndex: 'index'
}}

Which would output documents sorted by {a: 1, b: 1}, and inject an additional field 'index', containing it's position in the sort order (overwriting an existing field if applicable).

Original Description

Originally posted

https://groups.google.com/d/topic/mongodb-user/PL_g1RCmPsI/discussion

It would be very useful to be able to extract the indexed position of a document from a group statement, or a sort. e.g. consider the use case of building up a leader board / league table.

Workaround suggestions welcome in the meantime.



 Comments   
Comment by Joe Kanaan [ 01/Jun/21 ]

This can be achieved using $setWindowFields and $rank with a single sort field (SERVER-56572 will add support for the compound version)

Comment by David Percy [ 06/May/21 ]

The $rank window function should solve this, but in the current implementation it doesn't allow a compound sort (SERVER-56572).

{$setWindowFields: {
    sortBy: {a: 1, b: 1},
    output: {
        index: {$rank: {}}
    }
}}

Comment by Charlie Swanson [ 04/Feb/16 ]

I've changed the title/description of this ticket to restrict the scope to the position in the $sort. I believe Asya's comment above describes a way to do this using $unwind with the includeArrayIndex option.

However, it is still impossible to get the position in the sort, so this ticket will track that work.

Please feel free to leave a comment if you disagree with this change.

Comment by Asya Kamsky [ 15/May/13 ]

if SERVER-4588 was implemented that might be a workaround for this. Since you can basically group on a constant and build up an array with all documents (or at least known fields of interest) and then unwind with index if SERVER-4588 exists.

Comment by Eliot Horowitz (Inactive) [ 03/Jan/13 ]

We try not to do functions that only work non-sharded, as then people get quite surprised when they loose that functionality.

Will see if there is a way to implement this efficiently sharded.

Comment by Sam Halliday [ 03/Jan/13 ]

Eliot, exactly this is not the last step in a pipeline for me (there are further constraints and limits, etc).

I don't shard this collection, so that is not a concern. Happy for this to be a no-shards-allowed function.

Comment by Eliot Horowitz (Inactive) [ 03/Jan/13 ]

This could be interesting, but also problematic.
If that projection is the last step in a pipeline, then I'm not sure how much easier it is since you have the position in the client.

This could be very useful if the projection is not the last step.
i.e. do a sort, and the position, then re-sort based on something else.
Then you can see people sorted by name, with their position.

The problem is that requires consistency from all shards.
So it would be a scaling bottleneck.

Comment by Sam Halliday [ 03/Jan/13 ]

Hi Stephen,

Not sure what the "debugging with submitter" status means. I consider MR / JS console approaches to be (hacky) workarounds. I'd still like to see a feature to insert the position of a document (in an aggregation) into the document through a group or project.

Regards,
Sam

Generated at Thu Feb 08 03:16:27 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.