Filter on array filed in couchbase and couchbase lite


#1

I have series of documents, every document contains a field which it’s value is array of string.
now I want to filter value of this field.

{
    type : "customer",
    name: "customerX",
    states: [ "IL" , "IO" , "NY" , "CA" ]
}

I want to get customer documents which have “CA” in their states field.
I m using CBL Android.


#2

Create a view whose map function calls emit once for each item of states, with the item as the key. (The value can be nil, or the name if you want to display that.) Then query that view with key = "CA".


#3

Interesting this is really different paradigm thinking around data, specially for guys like us from SQL world.


#4

It’s a bit lower level, yeah. The best way to think about it is that you are generating an index, and every call to emit adds a key/value pair to it. So if you want to search for something (a state in your case) you need a view that emits all of that thing. And for each document it needs to emit every state represented by that document.


#5

What about dynamic SQL we generate, for instance consider a filter form which has all fields that user can filter ( customer name, tel, address, state,…)
in sql world I would generate a dynamic sql condition part by fields that user has filled out.

select *
from x
where [condition1] and [condition2] and [condition3]

conditions are optional and will be concatenate in runtime.
consider the number of options is predefined for example there are only 3 options which can participate in condition part and options are not infinite.
what is the best solution for these kind of situation?


#6

If you’re on iOS, you’d probably want to use the new CBLQueryBuilder class added in version 1.1 (which is about to ship.) It lets you construct queries using NSPredicate, similar to the way you do in Core Data.

Otherwise, you’d need to create a view/index for each of the possible conditions, then write some code to query multiple indexes and then find the documents that appear in each query result (to do the AND operation.)