[SERVER-69218] Feature request: Numerically-ordered fields / index Created: 28/Aug/22  Updated: 02/Sep/22  Resolved: 02/Sep/22

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

Type: Bug Priority: Major - P3
Reporter: Johnny Shields Assignee: Chris Kelly
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: PNG File image-2022-08-29-01-38-54-131.png     PNG File image-2022-08-29-01-41-13-152.png    
Operating System: ALL
Participants:

 Description   

How are you using Mongo? What version of the server and driver are you using?

MongoDB 5.x using in a Ruby application

What is the feature/improvement you would like?

I would like MongoDB to add better support for ordered lists across documents in a collection. This feature would allow the user to designate a field such as "position", and the DB would ensure the values of that field across documents remain in a monotonically-increasing integer sequence 0, 1, 2, 3, ... N.

I am the maintainer of a Ruby library called "Mongoid Orderable". This library makes numerically ordered field across documents. I'd like to ask MongoDB to investigate moving the functionality of this library to the server.

What use case would this feature/improvement enable?

This feature would be useful in a wide-variety of real world applications; ordered lists are extremely common. In my app (standard SaaS related to restaurant management) I have at least 20 instances of such ordered lists.

Suggested implementation

I propose to do this using a new "ordered" index type.

For example, suppose my application has many Car documents, and I want to maintain the "position" of the Cars in a consistent order for each Dealer.

I add the following index to the Car collection:

Cars -> createIndex({ dealer_id: 1, position: 1 }, { ordered: 1 })

Then in my Car collection, I see:

Car A -> dealer_id: xxx, position: 0
Car B -> dealer_id: xxx, position: 1
Car C -> dealer_id: xxx, position: 2
Car D -> dealer_id: yyy, position: 0
Car E -> dealer_id: yyy, position: 1

Due to this special "ordered" index, the position field will always maintain a zero-based ordered value for the given scope. If I modify any member, this ordering is preserved:

# Change Car C position
Car C -> updateOne(position: 1)
 
Car A -> dealer_id: xxx, position: 0
Car B -> dealer_id: xxx, position: 2 <-- updated
Car C -> dealer_id: xxx, position: 1 <-- updated
Car D -> dealer_id: yyy, position: 0
Car E -> dealer_id: yyy, position: 1
 
# Change Car C dealer_id
Car C -> updateOne(dealer_id: yyy)
 
Car A -> dealer_id: xxx, position: 0
Car B -> dealer_id: xxx, position: 1 <-- updated
Car C -> dealer_id: yyy, position: 1 <-- updated
Car D -> dealer_id: yyy, position: 0
Car E -> dealer_id: yyy, position: 2 <-- updated
 
# Change Car C position to be a very large value
Car C -> updateOne(position: 999)
 
Car A -> dealer_id: xxx, position: 0
Car B -> dealer_id: xxx, position: 1
Car C -> dealer_id: yyy, position: 2 <-- updated, stays in order
Car D -> dealer_id: yyy, position: 0
Car E -> dealer_id: yyy, position: 1 <-- updated
 
# Change Car C position to a non-numeric value
Car C -> updateOne(position: "foo")
# update is ignored; nothing changes

This feature should work with partial filter expressions, etc.



 Comments   
Comment by Johnny Shields [ 02/Sep/22 ]

Thanks for the response Chris. Hope MongoDB will consider this in the future it would make a lot of sense to solve at the DB level.

Comment by Chris Kelly [ 02/Sep/22 ]

Johnny,

Thank you for your feature request! I can see how this behavior would be valuable for ensuring values of that field across documents remain in a monotonically-increasing order. I've seen other reports of this being difficult to maintain in the application layer (especially due to race conditions).

This JIRA project is for bug reports, but you can help get this on the radar by submitting a feature request to our MongoDB Feedback Engine referencing these same suggestions (which it looks like you've already done!) Thank you for taking the time to report this, and also supporting the MongoDB community with your public repo.

We also evaluate external pull requests in the mongo repo on GitHub. 

Regards,
Christopher

 

Comment by Johnny Shields [ 28/Aug/22 ]

Here's a bit more color on this request. Here's 2 examples where I have sortable lists in my app (I have 20+ such cases). Even using MongoDB transactions, I've found that it's basically impossible to guarantee 100% integrity of the ordering across 10,000+ restaurants in my app. So I've added "Fix Sort Order" buttons that iterate over the documents and fix the sort ordering when it gets misaligned.

TLDR; I think this is clearly better solved at the database layer.

 

Generated at Thu Feb 08 06:12:53 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.