Multi-Criteria search with CouchDB

Question

How do I get data from CouchDB, filtering over multiple fields.

For example, if I have a person database with fields like Name, State, Country etc; and a search form on a web page, How do I get the data from CouchDB, considering only the non-null conditions.

in SQL, i would append conditions to the where clause WHERE Person.Name="John" AND Person.State in ("NY","CA"), but How do I frame this query as a CouchDB View

Answer 1

In CouchDB you use map/reduce Views. In SQL you have to explicitly say for which field index will be create. In CouchDB you write custom function creating index, so it can be more specific for your needs. If you want the index for such a simple thing as a search with name, state and country fields the view is just a map function:

function (doc) {
  if (doc.name && doc.state && doc.country)
    emit([doc.name, doc.state, doc.country], doc);
}

To search using this view you search for the key ["my_name", "my_state", "my_country"]. You can use it for querying with subset of name, state and country as long as they are a prefix of emitted array (like, searching with name but not with state and country) because the searchable result of map is sorted lexicographically.

In principle, the view is index with some capabilities of the queries, not as flexible as SQL queries, though. They are executed once and stored on disk, and incrementally calculated for new/modified data. Mind that it is hard to do things that are inefficient in the distributed system (for which CouchDB is designed): more complicated joins, searching without index... Although, in many cases artificial division for tables in relational model in not necessary when structured documents are available and some of the joins are not needed.

For some brief comparison of CouchDB vs. SQL see this chapter of The Definitive Guide book and other chapters and the official wiki for more information about the views.