Query Enhancements

This is a proposal, not an existing or planned feature. Although it might become one.

The Problem

Couchbase Lite map/reduce queries are less flexible than SQL queries. They correspond to an index lookup, and while SQL databases perform the same kinds of lookups, their query engines can also do a lot of post-processing.

For example, it's often noted that a map/reduce query can't select a range of rows ordered by one key and then sort the results by a different key. The only sorting is by the keys emitted by the map function. (For example, you can't query for candy bars whose names start with "M", ordered by calorie count. The query will only return them in alphabetical order.)

A SQL database will happily evaluate SELECT FROM candy WHERE name like 'M*' ORDER BY calories. It will use a by-name index lookup to find the results, and then sort them in memory by the value of the calories column. There's no magic: it's not that the relational database has more advanced indexing, it's just doing some less-efficient post-processing for you.

There's no reason Couchbase Lite couldn't do some of that too. I'm not talking about a query language, just the ability to add some additional parameters to a query to make it more flexible. Again, it's not rocket science, but it would make learning and using the database easier.

The Proposal

I propose we add some additional properties to the Query class.

Sorting

The sort property specifies an in-memory sort of the query rows, by properties of those rows. Its value is an array of strings: the first string is the primary sort, the second the secondary sort, etc. The sort strings are interpreted as follows:

• Each string is a JSON-Path expression relative to the QueryRow.
• If a string omits the $. prefix, it will be implicitly inserted; so a string can start with a property name.
• If a string is prefixed with a minus sign, the sort will be reversed (descending).

The QueryRow is not a JSON object, but for purposes of these paths it's considered to have the following top-level properties:

• key: The emitted key
• value: The emitted value
• doc: The document that emitted the row

For example, the default sorting is ["$.key"] or ["key"]. To sort by descending calories property of the emitted value, with grams of fat as secondary sort, use ["-value.calories", "-value.fat"].