SQL Not Like("abc%") and Not Equal (<> )queries in CouchBase


#1

Hello,
I am very new in CouchBase and wonder whether there is a way in Couchbase to get the same results as the queries generate.

Query 1:
select field1,field2
form mytable
where field3 not like ("%abc%")
or field3 like ("%xyz%")

Query 2:
select field1,field2
from mytable
where field4 <> 123456

Regards,


#2

In the map/reduce function there is on way to handle a SQL of WHERE Column_name LIKE %abc% because this is a regular expression operator that runs across the whole table. Map/reduce is pre-index query system. I Would suggest the Elasticsearch Plugin for more search options.


#3

Hello,

One important thing to remember when working with queries in Couchbase; they are done against a index that is built using map reduce functions (views). This index is created/updated incrementally each time you modify the data, and this index is distributed on all the nodes of your cluster.

So when you do a query you only request on ONE single index, from “left to right” in this index. (this means for example you do not have a way to do an AND or OR on two different columns.

Let’s say for example you have documents like :

{
“first” : “John”,
“last” : “Doe”,
}

If you want to search of last name you create a view like this one :

function map(doc, meta) {
emit(doc.last);
}

This create an index sorted using the Unicode collation see this blog post.

About the “LIKE”:
So if you want to find all people starting by “D” (more or less equivalent to the “Like ‘D%’”, you have do query the view with the following paramaters:

startkey="D"
endkey=“D\uefff”

Regards
Tug
@tgrall

Regards
Tug
@tgrall