CouchDB and starts with queries

Thought we could take a quick look at how to do a SQL'ish starts with LIKE 'Prefix%' query in CouchDb, using startkey, endkey & inclusive_end.

Given a couple of simple documents:

{
   "_id": "m1",
   "_rev": "...",
   "v": "a"
}
{
   "_id": "m2",
   "_rev": "...",
   "v": "ab"
}
{
   "_id": "m3",
   "_rev": "...",
   "v": "abc"
}
{
   "_id": "m4",
   "_rev": "...",
   "v": "b"
}

and a simple secondary index, having a simple map:

function(doc) {
  emit(doc.v);
}

we can query the view without any parameters and then be getting the result:

GET: /mydb/_design/mydesigndoc/_view/myview
=>
{
    "total_rows": 4,
    "offset": 0,
    "rows": [
        {
            "id": "m1",
            "key": "a",
            "value": null
        },
        {
            "id": "m2",
            "key": "ab",
            "value": null
        },
        {
            "id": "m3",
            "key": "abc",
            "value": null
        },
        {
            "id": "m4",
            "key": "b",
            "value": null
        }
    ]
}

Ok, so what if we would like to mimic key LIKE 'a%'? We need to do a range query using startkey and endkey. Now a misstake some people do is to know which value is the next value outside the range, combined with inclusive_end=false (to take up to the end-key). In our case: startkey="a"&endkey="b"&inclusive_end=false since b is after a. This would put unneccessary complexity in your application. Knowing what is the "next" value. Instead you can make use of a high value unicode character ufff0 which would look like this:

GET: /mydb/_design/mydesigndoc/_view/myview?startkey="a"&endkey="aufff0"
=>
{
    "total_rows": 4,
    "offset": 0,
    "rows": [
        {
            "id": "m1",
            "key": "a",
            "value": null
        },
        {
            "id": "m2",
            "key": "ab",
            "value": null
        },
        {
            "id": "m3",
            "key": "abc",
            "value": null
        }
    ]
}

and that's how you can accomplish a starts with query.

//Daniel