Couchbase Lite Views Query with multiple keys

I have currently following kind of docs which contains name, created_time, modified_time.

For example:

{
    _id: abcd,
    type: event_grp,
    name: test1,
    created_time: 1460034371,
    modified_time: 1460034371,
    event_id: 1234
}

I need to frequently query this type of docs based on different conditions. As N1QL is not currently supported on the platform, I have created a view currently with the key as {<event_id>,<created_time>,<name>} for docs with type: event_grp.

Querying for the docs of type event_grp in the descending order (last created shown first) for a particular evnet_id with the query as:

query.setDescending(true);
query.setStartKey(Arrays.asList(eventId, new HashMap<String, Object>()));
query.setEndKey(Arrays.asList(eventId));

This works fine. But now I need to query with many different conditions.

For example,

  • Get the event_grp which is last modified
  • Get the event_grp which is last created
  • Get the event_grp list sorted by its name (A-Z)
  • Get the event_grp list having title starting with “A” etc.

Taking third option to explain, as per my current key which includes current_time at the second position. So, If I want to get the list sorted by name, What should I pass as the second argument in the query.setStartKey()? Can I query this scenario with my current key structure?

I know I can create multiple views to get this things done. But it affects the performance as mentioned in the documentation.
Can this be done with single view using key as an array? If yes, How can I query that view differently to get different results?

No, you need a separate view for each of those queries, primarily because they are searching different keys, while a view index has only a single ordering.

This is just like a relational db or N1QL, in which you’d need to create a separate index to optimize each query.

If you’re on iOS you should check out the CBLQueryBuilder class, which lets you create a query in a higher level way and takes care of creating the necessary views.

By creating different views, how the performance will be affected?

And why QueryBuilder not for Android? Any plan to include?

There’s some overhead for creating and updating multiple indexes. But there’s no alternative (in any database engine.)

QueryBuilder is closely tied to some classes and idioms of Apple’s Cocoa frameworks, like NSPredicate, NSExpression, NSSortDescriptor. We plan to have comparable high-level query tools for each platform, but they need to be designed and implemented separately to fit in. For C# we plan to integrate with LINQ; for Java it’s less clear-cut because there isn’t a standard Java API for doing this kind of generic querying.

(Porting N1QL isn’t feasible in the near term, because the existing implementation is in a language with almost no mobile support (Go) and is designed to work with Couchbase Server’s indexing engine. It would have to be rewritten from scratch. A good metaphor is that you wouldn’t port Oracle’s SQL implementation to iOS or Android; instead you use SQLite, which is a different implementation tuned for smaller environments.)

As a user when you migrate from SQLite to couchbase for offline first application, a query is must have feature. However, creating indexing make it little harder for better performance. However, I do understand the difficulties to get N1QL written in Go to add support for Java. As seen here, there is some support being added for Android and iOS from Go.

I would be hoping you make the way to introduce N1QL for Android as it really makes app development a lot easier than with indexes and queries.