How to do number range search?

I have documents like these:

#1 { “start”: 99, “end”: 110, “country”: “US” }
#2 { “start”: 112, “end”: 126, “country”: “CA” }

If a value falls between “start” and “end”, inclusive, I want to select that document. For example, if the value is 100, I want to select document #1. In SQL, this will be:

select * from Document if start<=? and end >= ?

Is this possible to do using views in Couchbase?

Hi there,

This is indeed possible within Couchbase views!

I have done some testing and I think the best way to do this is to utilise the Map function capabilities.

So - the end product is, we need to take the document’s “Value”, and make sure that it is Above the ‘Start’ but Below the ‘end’. To do this, I have added another field into your JSON structure that explicitly contains the “value” we need to compare.

The JSON structure of my document now looks like this - with the added “Value” attribute:

{ "value": 103, "start": 99, "end": 110, "country": "US" }

So, to find if the “value” is between the “start” and “end”, we can write a simple Map function as follows:

function (doc, meta) { if (doc.value > doc.start && doc.value < doc.end) { emit(meta.id, doc.value); } }

Here, I am doing exactly as stated above: If the value is between the start and end, output the document’s key (id). I have also added ‘doc.value’ as the output value of the Map function, merely for fun. But if you also did this, you could use the doc.Value for many more things including stats etc.

I added two docs to my bucket, both with Values between the Start and End, and both are outputted, as they should be, when I execute my view code. This is what I see, and you should see, in your Admin UI:

http://i.rbin.co/rh0K

I hope this helps!

Robin.

Thanks for posting. But, I think you misunderstood my question. I can not store the value within the document. The value is something that is used to search for documents. May be another example will help. Let’s say, I have these documents that describe nature of various price ranges.

{"minPrice": 0, "maxPrice": 10, "Type":"Cheap"} {"minPrice": 11, "maxPrice": 20, "Type":"Moderate"} {"minPrice": 21, "maxPrice": 100, "Type":"Expensive"}

For any given price, I want to query these documents and find out if it is cheap, moderate or expensive. For example, if price is 5, I should get the “Cheap” document back.

Apologies, as you didn’t state where the value was coming from, I assumed it would be a fixed attribute within a document. In this case: I don’t see this as something that should be done within a Map function, but more something that should be done from your application logic.

In my mind, you would have to loop through each document comparing the values, so if there are a lot of Start and End ranges, it could become quite expensive.

Actually, this is possible in Couchbase as follows. To recap, the documents are:

#1 { "start": 99, "end": 110, "country": "US" } #2 { "start": 112, "end": 126, "country": "CA" }

Index the end field:

function (doc, meta) { emit(doc.end, null); }

To find out which document covers a value, say 100, do this query:

startkey=100&endkey=999999&limit=1

Where endkey is a very high number, larger than the largest “end” field.

This query will return the document with startkey <= 100 and endkey >= 100, assuming there are no gaps in the document. If there are gaps, then you can trivially reject the returned document by comparing the “start” field with the value.