Simulating SQL BETWEEN quuery

Couchbase Server
1

Hi,
I am new to Couchabse/NoSQL. I have a use case where I need to migrate an app from MySQL to Couchbase.

In MySQL i have a table with columns like startNumber,endNumber,LocationId
I fire a query as below- all values are long numbers

SELECT LocationId FROM blocks
WHERE {number} BETWEEN blocks.startNum AND blocks.endNum
LIMIT 1

where number is the runtime variable

In couchbase, I created a document with id being a GUID and LocationId, startNum and endNum being the JSON fields

How do i fire above query?

I am thinking on one option-
Create 2 views
view-1 - emit(doc.endNum , doc.LocationId );
View-2 emit(doc.startNum , doc.LocationId );

fire 2 queries, one on view-1 with startKeynumber

iterate thru fisrt set of records and find the locationId in second set of records, if find a match, stop.

Please note, range are not overlapping .example

0,5,99
6,10,100
11,15,101
16,20,102
21,25,103
26,30,104

But this means processing on application side. I want to process on couchbase server and give me a result, like MySQL

2

Your on the right track.
you should emit both the doc.endNum and doc.startNum in the Map function . it will create a result like this.

[“Key” , “Value”]
[“0” ,“99”]
[“5” , “99”]
[“6” , “100”]
[“10”,“100”]

To do the MySQL BETWEEN part you need to Query(View) like this
http://{Your IP}:8092/{Name of vBucket}/_design/{Name of Design}/_view/{Name of View}?startkey=“0”&endkey="6"
this Query(View) will return

[“0” , “99”]
[“5” , “99”]
[“6” , “100”]

NOTE* I see that you have LIMIT1 in your MySQl Query do you only want one result?

3

Thanks

Actually, In my use case, i do not know the lower and upper bound. I need to find the range it falls in, given a number. Anyway, i solved it like following
View is like this
function (doc, meta) {
if(doc.type=‘blocks’)
emit(doc.endNum, doc.locationId);
}

and then query with startKey={number} and limit=1

and yes, ranges are not overlapping. A number can only fall in one range.