CouchDB sorting and filtering in the same view

Question

I'm trying to use CouchDB for a new app, and I need to create a view that sorts by multiple fields and also filters by multiple fields. Here is an example document, I've left out the _id and _rev to save myself some typing.

{
    "title": "My Document",
    "date": 1279816057,
    "ranking": 5,
    "category": "fun",
    "tags": [
        "couchdb",
        "technology"
    ],
}

From the documentation, I've learned that I can easily create a view that sorts by a field such as ranking.

function(doc) {
    emit(doc.ranking, doc);
}

I've also learned that I can easily filter by fields such as category

function(doc) {
    emit(doc.category, doc);
}

http://127.0.0.1:5984/database/_design/filter/_view/filter?key=%22fun%22

My problem is that I need to do a bunch of these things all at the same time. I want to filter based on category and also tag. I should be able to filter down to only documents with category of "fun" and tag of "couchdb". I want to sort those filtered results by ranking in descending order, then by date in ascending order, then by title in alphabetical order.

How can I create one view that does all of that sorting and filtering combined?

Solution

For emitting more than one piece of data in a key, you'll want to read up on Complex Keys. You'll most likely end up emit()'ing a key that is an array made up of the category and tag. For example...

function(doc) {
  for(var i = 0; i < doc.tags.length; i++)
    emit([doc.category, doc.tags[i]], doc);
}

Now when you query ?key=["fun", "couchdb"] you'll get all the items in the fun category tagged with "couchdb". Or if you want all of the items in the fun category, regardless of their tag, then you can query with a range: ?startkey=["fun"]&endkey=["fun", {}]. Just remember, if your item has multiple tags, you'll get it multiple times in the results (because you emit()'d the doc once per tag).

To go the extra step of sorting by rating, date, and title you'll add two more elements to your array: an integer and either the ranking, date, or title. Remember, you can emit() more than once per map function. An example map function...

function(doc) {
  for(var i = 0; i < doc.tags.length; i++)
  {
     emit([doc.category, doc.tags[i], 0, doc.ranking], doc);
     emit([doc.category, doc.tags[i], 1, doc.title], doc);
     emit([doc.category, doc.tags[i], 2, doc.date], doc);
  }
}

Now your key structure is: ["category", "tag", 0 ... 2, rank/title/date]

You're basically grouping all of the rankings under 0, titles under 1, and dates under 2. Of course, you're transmitting a lot of data, so you could either break each of these groupings out into a separate view in your design document, or only return the doc's _id as the value (emit([ ...], doc._id);).

Get everything in the "fun" category with the "couchdb" tag (ascending):

?startkey=["fun", "couchdb"]&endkey=["fun", "couchdb", {}, {}]

Get everything in the "fun" category with the "couchdb" tag (descending):

?startkey=["fun", "couchdb", {}, {}]&endkey=["fun", "couchdb"]&descending=true

Get only rankings in the fun category with the couchdb tag (ascending):

?startkey=["fun", "couchdb", 0]&endkey=["fun", "couchdb", 0, {}]

Get only rankings in the "fun" category with the "couchdb" tag (descending):

?startkey=["fun", "couchdb", 0, {}]&endkey=["fun", "couchdb", 0]&descending=true

I hope this helps. Complex keys start to really show how powerful Map/Reduce is at slicing and dicing data.

Cheers.

Comments

[Apreche]

Thanks this is really helpful, but I just need a little bit more help. Since it's emitting the same documents over and over, how do I get it to just give me each document once?

[Sam Bisbee]

You can't, unless you break each of these groupings out into their own view. For example, /_design/articles/_view/byRanking, /_design/articles/_view/byDate, etc. If you want to keep everything in this one view, then you'll have to manage the data in your client. Or you could follow what I was saying about only returning the document's _id or null as the value, as you could then make a second call to get the document once.

[Apreche]

Oh wait, never mind. I figured it out. I think you slightly misunderstood what I wanted. I want to sort by ranking, date, and title simultaneously. In SQL it would be "order by ranking asc, date desc, title asc" I can do this by doing just one emit and use the startkey and endkey to filter, and the ordering will already be done. Thanks!

[user720491]

kinda late to the party, but how to deal with partial string matches? stackoverflow.com/questions/41608361/…