complex startkey/endkey query concerning PouchDB not returning expected result

Question

I have created an index called session_indexes/by_lastModifiedDate_status in pouchdb to emit both a timestamp as well as a string, which indicates the status.

Here are the array keys that get emitted when the map function is run with 6 documents in the database:

[1404446400000, 'SUSPENDED']
[1409630400000, 'OPEN']
[1413777600000, 'OPEN']
[1423976400000, 'CLOSED']
[1425704400000, 'OPEN']
[1430193600000, 'OPEN']

Now, I want to query this index using startkey and endkey. I do so by providing the following:

startkey: [1422766800000, 'CLOSED']
endkey: [1427688000000, 'CLOSED']

This means I want to find all of the documents that have a date between these two timestamps and have a CLOSED status.

However, PouchDB seems to return only the results that match the dates - so it's returning 2 results (the other one being [1425704400000, 'OPEN']).

The map function I am using is as follows. I know it looks strange - but this is actually being generated by code. It was not written by a human. But it still emits the correct keys just fine:

function(document) {
  if(document._id.startsWith('session')) {
    var keys = [];

    if(document.lastModifiedDate) {
      keys.push(document.lastModifiedDate);
    }

    if(document.status) {
      keys.push(document.status.text.toUpperCase());
    }

    emit(keys, null);
  }
}

The query itself is as follows:

return Database.instance().query('session_indexes/by_lastModifiedDate_status', {
    startkey: [1422766800000, 'CLOSED'],
    endkey: [1427688000000, 'CLOSED'],
    include_docs: true
}).then(function(result) {
    return _(result.rows).map(function(row) {
        return Session.fromDocument(row.doc, new Session());
    });
});

EDIT: It would appear that if I reverse the order and put the status first, the query works as expected. Can I ask why? And how can I fix that?

I'd appreciate any help you can provide me. Thanks!

nlawson nlawson · Accepted Answer · 2015-04-02T10:24:40

It works that way because of CouchDB collation ordering. E.g. for numbers+letters, you'd have:

[1, A] [1, B] [2, A] [2, B]

etc.

complex startkey/endkey query concerning PouchDB not returning expected result

1 Answers