danielwertheim

danielwertheim


notes from a passionate developer

Developer that lives by the mantra "code is meant to be shared".

Share


Tags


Disclaimer

This is a personal blog. The opinions expressed here represent my own and not those of my employer, nor current or previous. All content is published "as is", without warranty of any kind and I don't take any responsibility and can't be liable for any claims, damages or other liabilities that might be caused by the content.

CouchDB and starts with queries

Daniel WertheimDaniel Wertheim

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

Developer that lives by the mantra "code is meant to be shared".

Comments