Recently I had to sort a CouchDB view based on date while filtering that view by user and status.
In CouchDB, I knew that sorting of view results is based upon the key. Therefore, I needed a way to filter by part of a complex key ( e.g. field1 and field 2 from [field1, field2, field3], which in my case would have been user and status from [user, status, date] ). But where do you go with a question like this if the internet doesn’t want to play ball? IRC of course!
You have to use startkey= & endkey= if you want to filter by part of a complex key. If you want to filter using just key=, all parts of the complex key must be specified or you will get a null result, as key= is looking for an exact match.
Note that when filtering by part of the complex key, you can only filter by in-order combinations. For example, if you had [field1, field2, field3] as a key, you could only filter by [field1], [field1, field2] or [field1, field2, field3]. You could not, for example, filter by [field1, field3], as CouchDB would interpret the key you specified for field3 as the value to filter field2 by.
The syntax required to use startkey=...&endkey=... when you want to filter on only part of a complex key is as follows:
Say we had a key like [field1, field2, field3], and we wanted to filter on only field1 where field1 = "123". Our url would look like:
http://localhost:5984/database/_design/design_doc/_view/view_name?startkey=["123"]&endkey=["123",{}]
Notice the {} in the endkey. This is so that we get all values returned by the view between null and {}, which for just about every case should be everything.
