[SERVER-153] javascript sort helper for custom sorting / indexing Created: 13/Jul/09  Updated: 22/Mar/23

Status: Backlog
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Eliot Horowitz (Inactive) Assignee: Backlog - Query Integration
Resolution: Unresolved Votes: 106
Labels: js-in-agg, udf
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
is duplicated by SERVER-4080 Custom Sort Function Closed
Related
related to SERVER-14784 Add expression indexes Backlog
related to SERVER-5751 sort query results by distance to a p... Backlog
Assigned Teams:
Query Integration
Participants:

 Comments   
Comment by Qiang Li [ 06/Jul/16 ]

I know this is irrelevant. I tried PostgreSQL. It has got great jsonb support since 9.4. what's more, you can have the best of both worlds.
https://www.postgresql.org/docs/9.4/static/datatype-json.html

Comment by Billy Tetrud [ 06/Jul/16 ]

Being able to create custom indexes would be incredibly helpful. The most general way I can see this being done is something like this:

  // create the index
  db.MyCollection.createNamedIndex({name: "mod5", $expr:function(doc) {
    return doc.value%5
  }))
 
  // use the index
  db.MyCollection.find({x:5, $or[{value:100}, {$namedIndex: {name:'mod5', match: {$lt:1}}}]}).sort({$namedIndex: {name:'mod5', direction: -1}})

The named index would map the result of the given expression to the _ids with that result. You could then use that index for searching or sorting. This would be incredibly and open a huge number of potential optimizations without being required to store that data in each individual document.

Comment by Diego Pamio [ 06/May/16 ]

We need it for the following use case:

var myPerceptualHash = '0239582305980';
db.coll1.find().sort({ 
    $by : function( a, b ) {
        var hummingDistance = require('humming-distance');
        return hummingDistance(myPerceptualHash, a.phash) < hummingDistance(myPerceptualHash, b.phash;
    }
}).limit(1)

Comment by Qiang Li [ 05/Feb/15 ]

We would like to sort by computed values based on some time related fields. e.g. a document will be listed at the top if today is in its date range. it will not be practical to store precomputed values.

Comment by Lukas Gutschmidt [ 12/Jan/13 ]

Something like Views in Couchbase. I need that so badly.

Comment by Ivan Hristov [ 25/Oct/12 ]

This will be really a great feature. I'm especially interested in transforming a string field representing a number and then sorting by the number.

Comment by stephane erbrech [ 14/Feb/12 ]

Ok, thanks for details. That would for sure be useful. though overkill for my use case that only needs to return null last when sorting.

Comment by rgpublic [ 14/Feb/12 ]

@Stephane: No, the title says "custom sorting / indexing". This feature, at least as far as I understand it (@Eliot: please correct me if I'm wrong), means that one would be able to create an index on the functional result of any javascript calculation and subsequently make use of it during a query. Right now, for instance, we are using a property named "folder" with values like "this/is/a/path". At the moment we cannot query the name of the last folder ("path") efficiently. Sorting by the last folder would be similarly problematic. Thus, we have created a second property "_auto_foldername" and try to keep this in sync. You need to do this everywhere in your code where the property changes. Admittedly not really a great solution. Implementing this feature would allow us to get rid of these unnecessary helper properties (we got a lot of them) and just create a special index on say folder.substr(folder.lastIndexOf("/")). Now, the interesting question would be how Mongo will detect when it can use that index. I suppose the easiest most-straightforward method would be to do a hash fingerprint of the function and just compare that. Furthermore, the function would have to be deterministic (i.e. using no random numbers etc). In Oracle you have to guarantee that by adding the keyword DETERMINISTIC to your PL/SQL function. Don't know if we really need that. Oracle isnt able to check if this is true anyway. It's just a keyword.

Comment by stephane erbrech [ 14/Feb/12 ]

Wouldn't this mean tha mongodb will have to iterate over the whole collection to sort the result?
This is rarely a good option.

@NickHoffman to do this, I think there could/should be a much simpler sort option, like in SQL : null last, or null first.

Comment by Nick Hoffman [ 06/Feb/12 ]

That would be a very, very useful improvement to MongoDB, then. Thanks, Eliot.

Comment by Eliot Horowitz (Inactive) [ 06/Feb/12 ]

@nick - yes

Comment by Nick Hoffman [ 03/Feb/12 ]

Could this be used to sort alphabetically on a field, and put documents with a null value at the end of the result set?

Comment by Wei Kong [ 22/Oct/11 ]

I have an array defined. For example:

[

{"name":"food", "value":1}

,

{"name":"car", "value":2}

]

I would like to be able to sort by the value but on those with name either match "food" or "car"

Comment by Stanislav O. Pogrebnyak [ 27/Sep/11 ]

I see that some thing like:

db.coll1.find().sort({
$by : function( a, b )

{ return a._id < b._id }

}).limit(100)

I could even add tests, or help with implementation, since i have some C++/STL/boost backgrounds.

That could be very useful feature

Comment by Nic Cottrell (Personal) [ 14/May/11 ]

I would like to be able to sort the results based on a parameter, in this case the length of an example sentence I am doing a fuzzy match for. I'd like to be able to sort by something like:

"-Math.abs(textLength-28)"

and be able to pass a parameter instead of a hardcoded value.

Comment by Keith Branton [ 23/Feb/11 ]

I'd love to see a little detail on what this means.

If this is going to allow the use of expressions in sorts and "function indexes" then I wish I had more than one vote!

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