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

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 Answers

« Back to question.

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

« Back to question.

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.