String expressions in query keys

[Kevin Lord]

Is it possible to use any kind of regular expressions or wildcards in string keys when querying a view? I would really like to match on portions of a string aside from the beginning.

eg. if I have a name property, with document that has the value "John Michael Smith", I would like to query using a key "*Smith" or "*Michael*" etc.

I see that there seems to be this ability in the new CBLQueryBuilder class, but from the documentation I don't see any way to do this using the traditional view and query setup. I'm using this in Swift on iOS if it matters.

Thanks for any assistance.

[Jens Alfke]

On Feb 2, 2015, at 5:36 PM, Kevin Lord <lor...@gmail.com> wrote:

Is it possible to use any kind of regular expressions or wildcards in string keys when querying a view? I would really like to match on portions of a string aside from the beginning.

In general, you can do this by querying the entire view (i.e. not setting any startKey or endKey) and then using a postFilter predicate to do the string matching.

That may sound inefficient — and it is! — but that kind of search is inevitably inefficient since it can't use an index to narrow down the keys. (A SQL query would do exactly the same thing, behind the scenes.)

eg. if I have a name property, with document that has the value "John Michael Smith", I would like to query using a key "*Smith" or "*Michael*" etc.

A much more efficient way to do a search like this would be to make your map function emit each component of the name as a separate key. For example, something like

emit(doc.firstName, nil);

if (doc.middleName) emit(doc.middleName, nil);

emit(doc.lastName, nil);

(You don't have to store the name broken into components like that; if it's in a single string property, the map function can just split it by spaces and emit each component.)

Then you can query for a name component like "Smith" or "Michael" by setting that as the key, and find all instances of it.

—Jens

[Kevin Lord]

Thanks Jens, that makes sense.

As for your last example, that would allow me to do whole matches on the individual parts of the string. However, and this is stretching this particular example a bit, what if I wanted to match on a partial first name or last name? Say I've broken the name up as much as I can beforehand, and emitted the multiple keys, but I'd like to get all results that start with "Jon" including both "Jon" and "Jonathan". Also, potentially more difficult, I'd like to match the ending of an individual name component? Would I need to move to the postFilter predicate for this? 

[Kevin Lord]

Also, does the postFilter happen before or after reducing?

P.S. - Just a heads up. It seems the main documentation on the Couchbase Mobile site is a bit out of date. Had to check the docs on Github to find postFilter.

[Jens Alfke]

On Feb 3, 2015, at 5:56 AM, Kevin Lord <lor...@gmail.com> wrote:

Thanks Jens, that makes sense.

what if I wanted to match on a partial first name or last name? Say I've broken the name up as much as I can beforehand, and emitted the multiple keys, but I'd like to get all results that start with "Jon" including both "Jon" and "Jonathan".

You can do that with a prefix match. The standard idiom is to set startKey="Jon" and endKey="Jon\uFFFE" (or appending any other very large character value.) I was going to recommend the newish prefixLevel property that's available in iOS, but my colleague Pasin just found out it isn't working correctly in 1.0.3.

Also, potentially more difficult, I'd like to match the ending of an individual name component? Would I need to move to the postFilter predicate for this? 

The only fast way I can think of matching by ending is to emit the reversed name, and query on reversed keys.

Of course, these optimizations may not even be necessary. It depends on how many names there are likely to be in the database and how quickly you need the query to run. If this is some kind of personal address book, for example, with a couple of hundred names at most, it probably won't make any noticeable difference either way.

P.S. - Just a heads up. It seems the main documentation on the Couchbase Mobile site is a bit out of date. Had to check the docs on Github to find postFilter.

The main docs only cover features that are available cross-platform; new feature that aren't yet available on all platforms usually get documented in the Github wikis.

—Jens

[Kevin Lord]

I think we can get away with a postFilter, as the key will be an array, and the first item must be an exact match which should greatly limit the results.

Sorry, one more question. If I understand correctly, the postFilter step is done after any reduce, correct? So it can't be used to filter rows before counting them in a reduce? I would simply have to specify mapOnly = true and count the rows in the result myself?

Thanks so much for your help.

Kevin

[Jens Alfke]

On Feb 3, 2015, at 12:51 PM, Kevin Lord <lor...@gmail.com> wrote:

Sorry, one more question. If I understand correctly, the postFilter step is done after any reduce, correct? So it can't be used to filter rows before counting them in a reduce? I would simply have to specify mapOnly = true and count the rows in the result myself?

That's correct.

—Jens