Couch DB filter by key and sort by another field

Question

In couchdb I need to filter by key and which is done like this.

{
   "_id": "_design/test",
   "_rev": "6-cef7048c4fadf0daa67005fefe",
   "language": "javascript",
   "views": {
       "all": {
           "map": "function(doc) { if (doc.blogId) {emit(doc.key, doc);} }"
       }
   }
}

However the results should be ordered by another key (doc.anotherkey). So using the same function how do I achieve both filtering and ordering by another key.

Thank you

Answer 1

If you only need to query by single key, you can use the following map:

function (doc) {
  if (doc.blogId) {
    emit([doc.key, doc.anotherkey], 1);
  }
}

and query for "KEY" with ?startkey=["KEY"]&endkey=["KEY",{}]&include_docs=true.

Here, according to the collation specification of CouchDB:

• ["KEY"] is a value lesser than any ["KEY","OTHER"] value (because longer arrays sort after their prefixes), but greater than any ["KEY2","OTHER"] with "KEY2" < "KEY";
• and ["KEY",{}] is a value greater than any ["KEY","OTHER"] value, if doc.otherkey is never a JSON object (because JSON objects comes after any other JSON value), but lesser than any ["KEY2","OTHER"] with "KEY2" > "KEY".

Of course this is not limited to strings. Any type of value will work, as long as the collation is right.

Remember to URL encode the values in startkey and endkey. For example, using curl and assuming your database is "DB":

curl 'http://localhost:5984/DB/_design/test/_view/all?startkey=%5B%22KEY%22%5D&endkey=%5B%22KEY%22,%7B%7D%5D&include_docs=true'

Note that I've used the include_docs query parameter, instead of emitting the entire document with emit(..., doc), to save disk space. Query parameters are documented on CouchDB documentation.

To sort results in descending order, use the descending=true query parameter and swap the values of startkey and endkey as documented in the definitive guide book.

curl 'http://localhost:5984/DB/_design/test/_view/all?endkey=%5B%22KEY%22%5D&startkey=%5B%22KEY%22,%7B%7D%5D&include_docs=true&descending=true'