CouchDB case in-sensitive and OR options like in mysql

Question

I'm quite new with couchDB and I need a little support.

In MySQL I could run simply this query:

SELECT `name`, `id`, `desc` FROM `table` 
WHERE `name`="jack" OR `cat` LIKE "%|52224|%";

And here are my two problems:

I started to create a view (still without Like option and everything):

function(doc) {
    emit([doc.name, doc.cat], {
"name" : doc.name,
"desc" : doc.desc,
"id" : doc._id
});
}

1. When I use "emit([doc.name" the string must match 100% (also case sensitive).
-> How I make this option case un-sesnsitive?
That I can ask for ("Jack, jack, jAck, JAck,...) like in mysql?

2. How I create the OR option?

When I use [doc.name, doc.cat], I'm also forced to ask for both vars.
But when I have just one of them,
how I can query without creating for each option an own view?

Answer 1

To implement case insensitive search you just have to convert keys to lowercase:

emit([doc.name.toLowerCase(), doc.cat.toLowerCase()])

Now, if you convert your query to lowercase you'll have case insensitive matching. The problem with this solution is that if you want both case sensitive and case insensitive search you can either emit both values:
[doc.name.toLowerCase(), doc.name, doc.cat]
and use startkey and endkey to filter results, or create a separate view.

---

The second question is a bit more tricky to implement.
First of all, if you need to filter by doc.name only you can send request with startkey=["jack",0] and endkey=["jack",'zzzzzz'] which will return all documents with doc.name="jack" and doc.cat between 0 and 'zzzzzz' (there probably is a better way to say "any cat", unfortunately I can't find it right now).
If you need a real OR, then you should emit two rows for each document:
emit(doc.name, doc); emit(doc.cat, doc);

This way you can POST needed keys with your request: {"keys": ["jack", "cat_name"]}
This will return every document with either "jack" OR "cat_name" key. However documents that have both will be returned twice, so you have to filter duplicates in your application code.

---

You can also use couchdb-lucene which will solve both of your problems and probably a lot more. It is a popular choice among couchdb users for implementing advanced queries.