pouchdb: Cannot sort on field(s) "XXX" when using the default index

@garrensmith this behaviour should be explained more clear in the documatation incl. working examples, as it is hard to understand. I gues many people - like me - end up beeing really frustrated while trying to sort data.

Having exactly the same problem here. Any updates on this?

This issue not solved at this current version (“pouchdb-find”: “^7.2.2”): Cannot sort on field(s) “price” when using the default index […]node_modules\pouchdb-find\lib\index.js:1250:5

@garrensmith, I can understand that you want to mimic CouchDb but replacing { order: {$exists:true}} By { order: {$gt:true}} To get things working looks weird to me (or there’s something I didn’t get) and I’m not sure it comes from CouchDb… (And also it was tagged as bug by you 😃

Reading the plugin code:

// e.g. ['a'], ['a', 'b'] is true, but ['b'], ['a', 'b'] is false
function oneArrayIsSubArrayOfOther(left, right) {

// check that at least one field in the user's query is represented
// in the index. order matters in the case of sorts
function checkIndexFieldsMatch(indexFields, sortOrder, fields) {

Seems like the order of the index fields matter… Putting the order field first do work. I have not seen that documented anywhere else.

Working code:

await db.createIndex({
  index: { fields: ['createdAt', 'kind'] }, // createdAt must be first
});

await db.find({
  selector: { kind: 'foo' },
  sort: ['createdAt']
});

Yes that works! Thank you. So until the bug is fixed do the sort fields need to be first?

@RevillWeb that could be a bug. Could you try changing your index to be:

db.createIndex({
  index: {
    fields: ["amount", "title"]
  }
})

@wende60 I’m sorry you found this frustrating. I totally agree, this information needs to be in the docs. We are an opensource, volunteer community. So we don’t always have time to make everything perfect. It would be great if you could help us by adding the information in the docs.

@leolivier this will most likely not be fixed in pouchdb-find. We follow CouchDB’s design and it won’t be fixed there either. The best reason to explain why is to look at an example:

Say we have a database with 10 million documents in it. Then we do a query that retrieves 3 million documents using a predefined index. Now we want to sort the documents using a field that is not in the index. This would require CouchDB/PouchDB to load all those documents into memory and sort them. There is a large chance this would cause an out of memory operation and cause CouchDB to crash. Or in PouchDB’s situation, your browser to crash. We want to avoid this. So the only way sorting can happen is via the map/reduce index that is the start of the pouchdb-find. The sort then happens in the B-tree on disk and doesn’t require the whole database to be read into memory.

I hope that helps explain this situation a little more.

Hi All, I am still having this issue in PouchDB 6.2.0. Any help would be greatly appreciated. Here is my createIndex and find code:

db.createIndex({
  index: {
    fields: ["title", "amount"]
  }
}).then(function (result) {
  db.find({
      selector: { title: { $eq: "Mr" }, amount: { $gt: 0 } },
      sort: ["amount"]
    }).then(function (result) {
      console.log("RESULT:", result);
    }).catch(function (err) {
      console.log(err);
    });
}).catch(function (err) {
  console.log(err);
});

I always get the error “Cannot sort on field(s) “amount” when using the default index”, what am I doing wrong here? I’ve also tried amount: { $gt: true } and amount: { $exists: true }. I’m using Chrome 59.

If there isn’t an obvious mistake here let me know and I can create a working example.

Thank you!