Querying Couchbase Lite Databases

Query Format

Database queries have changed significantly. Instead of the map/reduce views used in 1.x, they’re now based on expressions, of the form "return ____ from documents where ____, ordered by ____", with semantics based on Couchbase’s N1QL query language.

There are several parts to specifying a query:

SELECT: Specifies the projection, which is the part of the document that is to be returned.
FROM: Specifies the database to query the documents from.
JOIN: Specifies the matching criteria in which to join multiple documents.
WHERE: Specifies the query criteria that the result must satisfy.
GROUP BY: Specifies the query criteria to group rows by.
ORDER BY: Specifies the query criteria to sort the rows in the result.

SELECT statement

With the SELECT statement, you can query and manipulate JSON data. With projections, you retrieve just the fields that you need and not the entire document.

A SelectResult represents a single return value of the query statement. You can specify a comma separated list of SelectResult expressions in the select statement of your query. For instance the following select statement queries for the document _id as well as the type and name properties of all documents in the database. In the query result, we print the _id and name properties of each row using the property name getter method.

{
    "_id": "hotel123",
    "type": "hotel",
    "name": "Apple Droid"
}

let query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("type"),
        SelectResult.property("name")
    )
    .from(DataSource.database(database))

do {
    for result in try query.execute() {
        print("document id :: \(result.string(forKey: "id")!)")
        print("document name :: \(result.string(forKey: "name")!)")
    }
} catch {
    print(error)
}

The SelectResult.all() method can be used to query all the properties of a document. In this case, the document in the result is embedded in a dictionary where the key is the database name. The following snippet shows the same query using SelectResult.all() and the result in JSON.

let query = QueryBuilder
    .select(SelectResult.all())
    .from(DataSource.database(database))

[
    {
        "travel-sample": {
            "callsign": "MILE-AIR",
            "country": "United States",
            "iata": "Q5",
            "icao": "MLA",
            "id": 10,
            "name": "40-Mile Air",
            "type": "airline"
        }
    },
    {
        "travel-sample": {
            "callsign": "TXW",
            "country": "United States",
            "iata": "TQ",
            "icao": "TXW",
            "id": 10123,
            "name": "Texas Wings",
            "type": "airline"
        }
    }
]

WHERE statement

Similar to SQL, you can use the where clause to filter the documents to be returned as part of the query. The select statement takes in an Expression. You can chain any number of Expressions in order to implement sophisticated filtering capabilities.

Comparison

The comparison operators can be used in the WHERE statement to specify on which property to match documents. In the example below, we use the equalTo operator to query documents where the type property equals "hotel".

{
    "_id": "hotel123",
    "type": "hotel",
    "name": "Apple Droid"
}

let query = QueryBuilder
    .select(SelectResult.all())
    .from(DataSource.database(database))
    .where(Expression.property("type").equalTo(Expression.string("hotel")))
    .limit(Expression.int(10))

do {
    for result in try query.execute() {
        if let dict = result.dictionary(forKey: "travel-sample") {
            print("document name :: \(dict.string(forKey: "name")!)")
        }
    }
} catch {
    print(error)
}

Collection Operators

Collection operators are useful to check if a given value is present in an array.

CONTAINS Operator

The following example uses the Function.arrayContains to find documents whose public_likes array property contain a value equal to "Armani Langworth".

{
    "_id": "hotel123",
    "name": "Apple Droid",
    "public_likes": ["Armani Langworth", "Elfrieda Gutkowski", "Maureen Ruecker"]
}

let query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("name"),
        SelectResult.property("public_likes")
    )
    .from(DataSource.database(database))
    .where(Expression.property("type").equalTo(Expression.string("hotel"))
        .and(ArrayFunction.contains(Expression.property("public_likes"), value: Expression.string("Armani Langworth")))
)

do {
     for result in try query.execute() {
        print("public_likes :: \(result.array(forKey: "public_likes")!.toArray())")
    }
}

IN Operator

The IN operator is useful when you need to explicitly list out the values to test against. The following example looks for documents whose first, last or username property value equals "Armani".

let values = [Expression.property("first"), Expression.property("last"), Expression.property("username")]

QueryBuilder
    .select(SelectResult.all())
    .from(DataSource.database(database))
    .where(Expression.string("Armani").in(values))

Like Operator

The like operator can be used for string matching.

The like operator performs case sensitive matches. So if you want to make the string matching case insensitive, you would have to use Function.lower or Function.upper to transform the matched string to lowercase or uppercase equivalents.

In the example below, we are looking for documents of type landmark where the name property exactly matches the string "Royal engineers museum". Note that since like does a case sensitive match, we use Function.lower to transform the matched string to the lowercase equivalent. So the following query will return "landmark" type documents with the name matching "Royal Engineers Museum", "royal engineers museum", "ROYAL ENGINEERS MUSEUM" and so on.

let query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("country"),
        SelectResult.property("name")
    )
    .from(DataSource.database(database))
    .where(Expression.property("type").equalTo(Expression.string("landmark"))
        .and(Function.lower(Expression.property("name")).like(Expression.string("Royal engineers museum")))
    )
    .limit(Expression.int(10))

do {
    for result in try query.execute() {
        print("name property :: \(result.string(forKey: "name")!)")
    }
}

Wildcard Match

We can use % sign within a like expression to do a wildcard match against zero or more characters. Using wildcards allows you to have some fuzziness in your search string.

In the example below, we are looking for documents of type "landmark" where the name property matches any string that begins with "eng" followed by zero or more characters, the letter "e", followed by zero or more characters. Once again, we are using Function.lower to make the search case insensitive.

The following query will return "landmark" type documents with name matching "Engineers", "engine", "english egg" , "England Eagle" and so on. Notice that the matches may span word boundaries.

let query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("country"),
        SelectResult.property("name")
    )
    .from(DataSource.database(database))
    .where(Expression.property("type").equalTo(Expression.string("landmark"))
        .and(Function.lower(Expression.property("name")).like(Expression.string("eng%e%")))
    )
    .limit(Expression.int(10))

Wildcard Character Match

We can use an _ sign within a like expression to do a wildcard match against a single character.

In the example below, we are looking for documents of type "landmark" where the name property matches any string that begins with "eng" followed by exactly 4 wildcard characters and ending in the letter "r". The following query will return "landmark" type documents with the name matching "Engineer", "engineer" and so on.

let query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("country"),
        SelectResult.property("name")
    )
    .from(DataSource.database(database))
    .where(Expression.property("type").equalTo(Expression.string("landmark"))
        .and(Expression.property("name").like(Expression.string("eng____r")))
    )
    .limit(Expression.int(10))

Regex Operator

Similar to wildcard like expressions, regex expressions based pattern matching allow you to have some fuzziness in your search string.

The regex operator is case sensitive.

In the example below, we are looking for documents of type "landmark" where the name property matches any string (on word boundaries) that begins with "eng" followed by exactly 4 wildcard characters and ending in the letter "r". The following query will return "landmark" type documents with name matching "Engine", "engine" and so on. Note that the \b specifies that the match must occur on word boundaries.

let query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("name")
    )
    .from(DataSource.database(database))
    .where(Expression.property("type").equalTo(Expression.string("landmark"))
        .and(Expression.property("name").regex(Expression.string("\\bEng.*e\\b")))
    )
    .limit(Expression.int(10))

Deleted Document

Starting in Couchbase Lite 2.5, you can query documents that have been deleted (tombstones). The following example shows how to query deleted documents in the database.

// Query documents that have been deleted
let query = QueryBuilder
    .select(SelectResult.expression(Meta.id))
    .from(DataSource.database(db))
    .where(Meta.isDeleted)

JOIN statement

The JOIN clause enables you to create new input objects by combining two or more source objects.

The following example uses a JOIN clause to find the airline details which have routes that start from RIX. This example JOINS the document of type "route" with documents of type "airline" using the document ID (_id) on the "airline" document and airlineid on the "route" document.

let query = QueryBuilder
    .select(
        SelectResult.expression(Expression.property("name").from("airline")),
        SelectResult.expression(Expression.property("callsign").from("airline")),
        SelectResult.expression(Expression.property("destinationairport").from("route")),
        SelectResult.expression(Expression.property("stops").from("route")),
        SelectResult.expression(Expression.property("airline").from("route"))
    )
    .from(
        DataSource.database(database!).as("airline")
    )
    .join(
        Join.join(DataSource.database(database!).as("route"))
            .on(
                Meta.id.from("airline")
                    .equalTo(Expression.property("airlineid").from("route"))
        )
    )
    .where(
        Expression.property("type").from("route").equalTo(Expression.string("route"))
            .and(Expression.property("type").from("airline").equalTo(Expression.string("airline")))
            .and(Expression.property("sourceairport").from("route").equalTo(Expression.string("RIX")))
)

GROUP BY statement

You can perform further processing on the data in your result set before the final projection is generated. The following example looks for the number of airports at an altitude of 300 ft or higher and groups the results by country and timezone.

{
    "_id": "airport123",
    "type": "airport",
    "country": "United States",
    "geo": { "alt": 456 },
    "tz": "America/Anchorage"
}

let query = QueryBuilder
    .select(
        SelectResult.expression(Function.count(Expression.all())),
        SelectResult.property("country"),
        SelectResult.property("tz"))
    .from(DataSource.database(database))
    .where(
        Expression.property("type").equalTo(Expression.string("airport"))
            .and(Expression.property("geo.alt").greaterThanOrEqualTo(Expression.int(300)))
    ).groupBy(
        Expression.property("country"),
        Expression.property("tz")
)

do {
    for result in try query.execute() {
        print("There are \(result.int(forKey: "$1")) airports on the \(result.string(forKey: "tz")!) timezone located in \(result.string(forKey: "country")!) and above 300 ft")
    }
}

There are 138 airports on the Europe/Paris timezone located in France and above 300 ft
There are 29 airports on the Europe/London timezone located in United Kingdom and above 300 ft
There are 50 airports on the America/Anchorage timezone located in United States and above 300 ft
There are 279 airports on the America/Chicago timezone located in United States and above 300 ft
There are 123 airports on the America/Denver timezone located in United States and above 300 ft

ORDER BY statement

It is possible to sort the results of a query based on a given expression result. The example below returns documents of type equal to "hotel" sorted in ascending order by the value of the title property.

let query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("title"))
    .from(DataSource.database(database))
    .where(Expression.property("type").equalTo(Expression.string("hotel")))
    .orderBy(Ordering.property("title").ascending())
    .limit(Expression.int(10))

Aberdyfi
Achiltibuie
Altrincham
Ambleside
Annan
Ardèche
Armagh
Avignon

Date/Time Functions

Couchbase Lite documents support a date type that internally stores dates in ISO 8601 with the GMT/UTC timezone.

Couchbase Lite 2.5 adds the ability to run date comparisons in your Couchbase Lite queries. To do so, four functions have been added to the Query Builder API:

Function.StringToMillis(Expression.Property("date_time")): The input to this will be a validly formatted ISO 8601 date_time string. The end result will be an expression (with a numeric content) that can be further input into the query builder.
Function.StringToUTC(Expression.Property("date_time")): The input to this will be a validly formatted ISO 8601 date_time string. The end result will be an expression (with string content) that can be further input into the query builder.
Function.MillisToString(Expression.Property("date_time")): The input for this is a numeric value representing milliseconds since the Unix epoch. The end result will be an expression (with string content representing the date and time as an ISO 8601 string in the device’s timezone) that can be further input into the query builder.
Function.MillisToUTC(Expression.Property("date_time")): The input for this is a numeric value representing milliseconds since the Unix epoch. The end result will be an expression (with string content representing the date and time as a UTC ISO 8601 string) that can be further input into the query builder.