QueryBuilder | Couchbase Docs

March 23, 2025

+ 12

Description — How to use QueryBuilder to build effective queries with Couchbase Lite on Android
Related Content — Predictive Queries | Live Queries | Indexing

The examples used here are based on the Travel Sample app and data introduced in the Couchbase Mobile Workshop tutorial

Introduction

Couchbase Lite for Android provides two ways to build and run database queries; the QueryBuilder API described in this topic and SQL++ for Mobile.

Database queries defined with the QueryBuilder API use the query statement format shown in Example 1. The structure and semantics of the query format are based on Couchbase’s SQL++ query language.

Example 1. Query Format

SQL
Copy
SELECT ____
FROM 'data-source'
WHERE ____,
JOIN ____
GROUP BY ____
ORDER BY ____

Query Components

Component	Description
SELECT statement	The document properties that will be returned in the result set
FROM	The data source to query the documents from - the collection of the database.
WHERE statement	The query criteria The `SELECT`ed properties of documents matching this criteria will be returned in the result set
JOIN statement	The criteria for joining multiple documents
GROUP BY statement	The criteria used to group returned items in the result set
ORDER BY statement	The criteria used to order the items in the result set

Component

Description

SELECT statement

The document properties that will be returned in the result set

FROM

The data source to query the documents from - the collection of the database.

WHERE statement

The query criteria
The `SELECT`ed properties of documents matching this criteria will be returned in the result set

JOIN statement

The criteria for joining multiple documents

GROUP BY statement

The criteria used to group returned items in the result set

ORDER BY statement

The criteria used to order the items in the result set

We recommend working through the query section of the Couchbase Mobile Workshop tutorial as a good way to build your skills in this area.

SELECT statement

In this section: Return Selected Properties | Return All Properties
Related: Handling result sets

Use the SELECT statement to specify which properties you want to return from the queried documents. You can opt to retrieve entire documents, or just the specific properties you need.

Return All Properties

Use the SelectResult.all() method to return all the properties of selected documents — see: Example 2.

Example 2. Using SELECT to Retrieve All Properties

This query shows how to retrieve all properties from all documents in your database.

Kotlin
Java

Kotlin
ViewCopy
val queryAll = QueryBuilder
    .select(SelectResult.all())
    .from(DataSource.collection(collection))
    .where(Expression.property("type").equalTo(Expression.string("hotel")))

Java
ViewCopy
Query query = QueryBuilder
    .select(SelectResult.all())
    .from(DataSource.collection(collection))
    .where(Expression.property("type").equalTo(Expression.string("hotel")));

The query.execute statement returns the results in a dictionary, where the key is the database name — see Example 3.

Example 3. ResultSet Format from SelectResult.all()

json
Copy
[
  {
    "travel-sample": { (1)
      "callsign": "MILE-AIR",
      "country": "United States",
      "iata": "Q5",
      "icao": "MLA",
      "id": 10,
      "name": "40-Mile Air",
      "type": "airline"
    }
  },
  {
    "travel-sample": { (2)
      "callsign": "ALASKAN-AIR",
      "country": "United States",
      "iata": "AA",
      "icao": "AAA",
      "id": 10,
      "name": "Alaskan Airways",
      "type": "airline"
    }
  }
]

1	The result for the first document matching the query criteria.
2	The result for the next document matching the query criteria.

See: Result Sets for more on processing query results.

Return Selected Properties

To access only specific properties, specify a comma-separated list of SelectResult expressions, one for each property, in the select statement of your query — see: Example 4

Example 4. Using SELECT to Retrieve Specific Properties

In this query we retrieve and then print the _id, type and name properties of each document.

Kotlin
Java

Kotlin
ViewCopy
val query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("name"),
        SelectResult.property("type")
    )
    .from(DataSource.collection(collection))
    .where(Expression.property("type").equalTo(Expression.string("hotel")))
    .orderBy(Ordering.expression(Meta.id))

query.execute().use { rs ->
    rs.forEach {
        log("hotel id ->${it.getString("id")}")
        log("hotel name -> ${it.getString("name")}")
    }
}

Java
ViewCopy
Query query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("name"),
        SelectResult.property("type"))
    .from(DataSource.collection(collection))
    .where(Expression.property("type").equalTo(Expression.string("hotel")))
    .orderBy(Ordering.expression(Meta.id));

try (ResultSet resultSet = query.execute()) {
    for (Result result: resultSet) {
        Logger.log("hotel id -> " + result.getString("id"));
        Logger.log("hotel name -> " + result.getString("name"));
    }
}

The query.execute statement returns one or more key-value pairs, one for each SelectResult expression, with the property-name as the key — see Example 5

Example 5. Select Result Format

json
Copy
[
  { (1)
    "id": "hotel123",
    "type": "hotel",
    "name": "Hotel Ghia"
  },
  { (2)
    "id": "hotel456",
    "type": "hotel",
    "name": "Hotel Deluxe",
  }
]

1	The result for the first document matching the query criteria.
2	The result for the next document matching the query criteria.

See: Result Sets for more on processing query results.

WHERE statement

In this section: Comparison Operators | Collection Operators | Like Operator | Regex Operator | Deleted Document

Like SQL, you can use the WHERE statement to choose which documents are returned by your query. The select statement takes in an Expression. You can chain any number of Expressions in order to implement sophisticated filtering capabilities.

Comparison Operators

The Expression Comparators 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".

json
Copy
[
  { (1)
    "id": "hotel123",
    "type": "hotel",
    "name": "Hotel Ghia"
  },
  { (2)
    "id": "hotel456",
    "type": "hotel",
    "name": "Hotel Deluxe",
  }
]

Example 6. Using Where

Kotlin
Java

Kotlin
ViewCopy
val query = QueryBuilder
    .select(SelectResult.all())
    .from(DataSource.collection(collection))
    .where(Expression.property("type").equalTo(Expression.string("hotel")))
    .limit(Expression.intValue(10))

query.execute().use { rs ->
    rs.forEach { result ->
        result.getDictionary("myDatabase")?.let {
            log("name -> ${it.getString("name")}")
            log("type -> ${it.getString("type")}")
        }
    }
}

Java
ViewCopy
Query query = QueryBuilder
    .select(SelectResult.all())
    .from(DataSource.collection(collection))
    .where(Expression.property("type").equalTo(Expression.string("hotel")))
    .limit(Expression.intValue(10));

try (ResultSet resultSet = query.execute()) {
    for (Result result: resultSet) {
        Dictionary all = result.getDictionary(collectionName);
        Logger.log("name -> " + all.getString("name"));
        Logger.log("type -> " + all.getString("type"));
    }
}

Collection Operators

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

CONTAINS Operator

The following example uses the ArrayFunction to find documents where the public_likes array property contains a value equal to "Armani Langworth".

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

Kotlin
Java

Kotlin
ViewCopy
val query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("name"),
        SelectResult.property("public_likes")
    )
    .from(DataSource.collection(collection))
    .where(
        Expression.property("type").equalTo(Expression.string("hotel"))
            .and(
                ArrayFunction.contains(
                    Expression.property("public_likes"),
                    Expression.string("Armani Langworth")
                )
            )
    )
query.execute().use { rs ->
    rs.forEach {
        log("public_likes -> ${it.getArray("public_likes")?.toList()}")
    }
}

Java
ViewCopy
Query query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("name"),
        SelectResult.property("public_likes"))
    .from(DataSource.collection(collection))
    .where(Expression.property("type").equalTo(Expression.string("hotel"))
        .and(ArrayFunction
            .contains(Expression.property("public_likes"), Expression.string("Armani Langworth"))));
try (ResultSet results = query.execute()) {
    for (Result result: results) {
        Logger.log("public_likes -> " + result.getArray("public_likes").toList());
    }
}

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".

Kotlin
Java

Kotlin
ViewCopy
val query = QueryBuilder.select(SelectResult.all())
    .from(DataSource.collection(collection))
    .where(
        Expression.string("Armani").`in`(
            Expression.property("first"),
            Expression.property("last"),
            Expression.property("username")
        )
    )

query.execute().use { rs ->
    rs.forEach {
        log("public_likes -> ${it.toMap()}")
    }
}

Java
ViewCopy
Expression[] values = new Expression[] {
    Expression.property("first"),
    Expression.property("last"),
    Expression.property("username")
};

Query query = QueryBuilder.select(SelectResult.all())
    .from(DataSource.collection(collection))
    .where(Expression.string("Armani").in(values));

Like Operator

In this section: String Matching | Wildcard Match | Wildcard Character Match

String Matching

The Like() operator can be used for string matching — see Example 7

The like operator performs case sensitive matches.
To perform case insensitive matching, use Function.lower or Function.upper to ensure all comparators have the same case, thereby removing the case issue.

This query returns landmark type documents where the name matches the string "Royal Engineers Museum", regardless of how it is capitalized (so, it selects "royal engineers museum", "ROYAL ENGINEERS MUSEUM" and so on).

Example 7. Like with case-insensitive matching

Kotlin
Java

Kotlin
ViewCopy
val query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("country"),
        SelectResult.property("name")
    )
    .from(DataSource.collection(collection))
    .where(
        Expression.property("type").equalTo(Expression.string("landmark"))
            .and(
                Function.lower(Expression.property("name"))
                    .like(Expression.string("royal engineers museum"))
            )
    )
query.execute().use { rs ->
    rs.forEach {
        log("name -> ${it.getString("name")}")
    }
}

Java
ViewCopy
Query query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("country"),
        SelectResult.property("name"))
    .from(DataSource.collection(collection))
    .where(Expression.property("type").equalTo(Expression.string("landmark"))
        .and(Function.lower(Expression.property("name")).like(Expression.string("royal engineers museum"))));

try (ResultSet resultSet = query.execute()) {
    for (Result result: resultSet) {
        Logger.log("name -> " + result.getString("name"));
    }
}

Note the use of Function.lower to transform name values to the same case as the literal comparator.

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 Example 8 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.

So "landmark" documents with names such as "Engineers", "engine", "english egg" and "England Eagle". Notice that the matches may span word boundaries.

Example 8. Wildcard Matches

Kotlin
Java

Kotlin
ViewCopy
val query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("country"),
        SelectResult.property("name")
    )
    .from(DataSource.collection(collection))
    .where(
        Expression.property("type").equalTo(Expression.string("landmark"))
            .and(
                Function.lower(Expression.property("name"))
                    .like(Expression.string("eng%e%"))
            )
    )
query.execute().use { rs ->
    rs.forEach {
        log("name -> ${it.getString("name")}")
    }
}

Java
ViewCopy
Query query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("country"),
        SelectResult.property("name"))
    .from(DataSource.collection(collection))
    .where(Expression.property("type").equalTo(Expression.string("landmark"))
        .and(Function.lower(Expression.property("name")).like(Expression.string("eng%e%"))));

try (ResultSet resultSet = query.execute()) {
    for (Result result: resultSet) {
        Logger.log("name ->  " + result.getString("name"));
    }
}

Wildcard Character Match

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

In Example 9 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 query returns "landmark" type documents with names such as "Engineer", "engineer" and so on.

Example 9. Wildcard Character Matching

Kotlin
Java

Kotlin
ViewCopy
val query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("country"),
        SelectResult.property("name")
    )
    .from(DataSource.collection(collection))
    .where(
        Expression.property("type").equalTo(Expression.string("landmark"))
            .and(
                Function.lower(Expression.property("name"))
                    .like(Expression.string("eng____r"))
            )
    )
query.execute().use { rs ->
    rs.forEach {
        log("name -> ${it.getString("name")}")
    }
}

Java
ViewCopy
Query query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("country"),
        SelectResult.property("name"))
    .from(DataSource.collection(collection))
    .where(Expression.property("type").equalTo(Expression.string("landmark"))
        .and(Function.lower(Expression.property("name")).like(Expression.string("eng____r"))));

try (ResultSet resultSet = query.execute()) {
    for (Result result: resultSet) {
        Logger.log("name -> " + result.getString("name"));
    }
}

Regex Operator

Similar to the wildcards in like expressions, regex based pattern matching allow you to introduce an element of fuzziness in your search string — see the code shown in Example 10.

The regex operator is case sensitive, use upper or lower functions to mitigate this if required.

Example 10. Using Regular Expressions

This example returns documents with a `type` of "landmark" and a `name` property that matches any string that begins with "eng" and ends in the letter "e".

Kotlin
Java

Kotlin
ViewCopy
val query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("country"),
        SelectResult.property("name")
    )
    .from(DataSource.collection(collection))
    .where(
        Expression.property("type").equalTo(Expression.string("landmark"))
            .and(
                Function.lower(Expression.property("name"))
                    .regex(Expression.string("\\beng.*r\\b"))
            )
    )
query.execute().use { rs ->
    rs.forEach {
        log("name -> ${it.getString("name")}")
    }
}

Java
ViewCopy
Query query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("country"),
        SelectResult.property("name"))
    .from(DataSource.collection(collection))
    .where(Expression.property("type").equalTo(Expression.string("landmark"))
        .and(Function.lower(Expression.property("name")).regex(Expression.string("\\beng.*r\\b"))));

try (ResultSet resultSet = query.execute()) {
    for (Result result: resultSet) {
        Logger.log("name -> " + result.getString("name"));
    }
}

1	The `\b` specifies that the match must occur on word boundaries.

For more on the regex spec used by Couchbase Lite see cplusplus regex reference page

Deleted Document

You can query documents that have been deleted (tombstones) ^[1] as shown in Example 11.

Example 11. Query to select Deleted Documents

This example shows how to query deleted documents in the database. It returns is an array of key-value pairs.

Kotlin
Java

Kotlin
ViewCopy
// Query documents that have been deleted
val query = QueryBuilder
    .select(SelectResult.expression(Meta.id))
    .from(DataSource.collection(collection))
    .where(Meta.deleted)

Java
ViewCopy
// Query documents that have been deleted
Query query = QueryBuilder
    .select(SelectResult.expression(Meta.id))
    .from(DataSource.collection(collection))
    .where(Meta.deleted);

JOIN statement

The JOIN clause enables you to select data from multiple documents that have been linked by criteria specified in the JOIN statement. For example to combine airline details with route details, linked by the airline id — see Example 12.

Example 12. Using JOIN to Combine Document Details

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.

Kotlin
Java

Kotlin
ViewCopy
val 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.collection(collection).`as`("airline"))
    .join(
        Join.join(DataSource.collection(collection).`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"))
            )
    )
query.execute().use { rs ->
    rs.forEach {
        log("name -> ${it.toMap()}")
    }
}

Java
ViewCopy
Query 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.collection(collection).as("airline"))
    .join(Join.join(DataSource.collection(collection).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"))));

try (ResultSet resultSet = query.execute()) {
    for (Result result: resultSet) {
        Logger.log(result.toMap().toString());
    }
}

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.

Data Model for Example

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

Example 13. Query using GroupBy

This example shows a query that selects all airports with an altitude above 300ft. The output (a count, $1) is grouped by country, within timezone.

Kotlin
Java

Kotlin
ViewCopy
val query = QueryBuilder
    .select(
        SelectResult.expression(Function.count(Expression.string("*"))),
        SelectResult.property("country"),
        SelectResult.property("tz")
    )
    .from(DataSource.collection(collection))
    .where(
        Expression.property("type").equalTo(Expression.string("airport"))
            .and(Expression.property("geo.alt").greaterThanOrEqualTo(Expression.intValue(300)))
    )
    .groupBy(
        Expression.property("country"), Expression.property("tz")
    )
    .orderBy(Ordering.expression(Function.count(Expression.string("*"))).descending())
query.execute().use { rs ->
    rs.forEach {
        log(
            "There are ${it.getInt("$1")} airports on the ${
                it.getString("tz")
            } timezone located in ${
                it.getString("country")
            } and above 300ft"
        )
    }
}

Java
ViewCopy
Query query = QueryBuilder.select(
        SelectResult.expression(Function.count(Expression.string("*"))),
        SelectResult.property("country"),
        SelectResult.property("tz"))
    .from(DataSource.collection(collection))
    .where(Expression.property("type").equalTo(Expression.string("airport"))
        .and(Expression.property("geo.alt").greaterThanOrEqualTo(Expression.intValue(300))))
    .groupBy(
        Expression.property("country"),
        Expression.property("tz"))
    .orderBy(Ordering.expression(Function.count(Expression.string("*"))).descending());

try (ResultSet resultSet = query.execute()) {
    for (Result result: resultSet) {
        Logger.log(String.format(
            Locale.getDefault(),
            "There are %d airports on the %s timezone located in %s and above 300ft",
            result.getInt("$1"),
            result.getString("tz"),
            result.getString("country")));
    }
}

The query shown in Example 13 generates the following output:

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 — see Example 14

Example 14. Query using OrderBy

This example shows a query that returns documents of type equal to "hotel" sorted in ascending order by the value of the title property.

Kotlin
Java

Kotlin
ViewCopy
val query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("name")
    )
    .from(DataSource.collection(collection))
    .where(Expression.property("type").equalTo(Expression.string("hotel")))
    .orderBy(Ordering.property("name").ascending())
    .limit(Expression.intValue(10))

query.execute().use { rs ->
    rs.forEach {
        log("${it.toMap()}")
    }
}

Java
ViewCopy
Query query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("name"))
    .from(DataSource.collection(collection))
    .where(Expression.property("type").equalTo(Expression.string("hotel")))
    .orderBy(Ordering.property("name").ascending())
    .limit(Expression.intValue(10));

try (ResultSet resultSet = query.execute()) {
    for (Result result: resultSet) {
        Logger.log(result.toMap().toString());
    }
}

The query shown in Example 14 generates the following output:

text
Copy
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’s Query Builder API ^[1] includes four functions for date comparisons.

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.

Result Sets

In this section: Processing | Select All Properties | Select Specific Properties | Select Document Id Only | Select Count-only | Handling Pagination

Processing

This section shows how to handle the returned result sets for different types of SELECT statements.

The result set format and its handling varies slightly depending on the type of SelectResult statements used. The result set formats you may encounter include those generated by :

SelectResult.all — see: All Properties
SelectResult.expression(property("name")) — see: Specific Properties
SelectResult.expression(meta.id) — Metadata (such as the _id) — see: Document ID Only
SelectResult.expression(Function.count(Expression.all())).as("mycount") — see: Select Count-only

To process the results of a query, you first need to execute it using Query.execute.

The execution of a Couchbase Lite for Android’s database query typically returns an array of results, a result set.

The result set of an aggregate, count-only, query is a key-value pair — see Select Count-only — which you can access using the count name as its key.
The result set of a query returning document properties is an array.
Each array row represents the data from a document that matched your search criteria (the WHERE statements) The composition of each row is determined by the combination of SelectResult expressions provided in the SELECT statement. To unpack these result sets you need to iterate this array.

Select All Properties

Query

The Select statement for this type of query, returns all document properties for each document matching the query criteria — see Example 15

Example 15. Query selecting All Properties

Kotlin
Java

Kotlin
ViewCopy
val listQuery = QueryBuilder.select(SelectResult.all())
    .from(DataSource.collection(collection))

Java
ViewCopy
Query listQuery = QueryBuilder.select(SelectResult.all())
    .from(DataSource.collection(collection));

Result Set Format

The result set returned by queries using SelectResult.all is an array of dictionary objects — one for each document matching the query criteria.

For each result object, the key is the database name and the 'value' is a dictionary representing each document property as a key-value pair — see: Example 16.

Example 16. Format of Result Set (All Properties)

json
Copy
[
  {
    "travel-sample": { (1)
      "callsign": "MILE-AIR",
      "country": "United States",
      "iata": "Q5",
      "icao": "MLA",
      "id": 10,
      "name": "40-Mile Air",
      "type": "airline"
    }
  },
  {
    "travel-sample": { (2)
      "callsign": "ALASKAN-AIR",
      "country": "United States",
      "iata": "AA",
      "icao": "AAA",
      "id": 10,
      "name": "Alaskan Airways",
      "type": "airline"
    }
  }
]

1	The result for the first document matching the query criteria.
2	The result for the next document matching the query criteria.

Result Set Access

In this case access the retrieved document properties by converting each row’s value, in turn, to a dictionary — as shown in Example 17.

Example 17. Using Document Properties (All)

Kotlin
Java

Kotlin
ViewCopy
val hotels = mutableMapOf<String, Hotel>()
listQuery.execute().use { rs ->
    rs.allResults().forEach {
        // get the k-v pairs from the 'hotel' key's value into a dictionary
        val thisDocsProps = it.getDictionary(0) (1)
        val thisDocsId = thisDocsProps!!.getString("id")
        val thisDocsName = thisDocsProps.getString("name")
        val thisDocsType = thisDocsProps.getString("type")
        val thisDocsCity = thisDocsProps.getString("city")

        // Alternatively, access results value dictionary directly
        val id = it.getDictionary(0)?.getString("id").toString() (2)
        hotels[id] = Hotel(
            id,
            it.getDictionary(0)?.getString("type"),
            it.getDictionary(0)?.getString("name"),
            it.getDictionary(0)?.getString("city"),
            it.getDictionary(0)?.getString("country"),
            it.getDictionary(0)?.getString("description")
        )
    }
}

Java
ViewCopy
Map<String, Hotel> hotels = new HashMap<>();
try (ResultSet resultSet = listQuery.execute()) {
    for (Result result: resultSet) {
        // get the k-v pairs from the 'hotel' key's value into a dictionary
        Dictionary docsProp = result.getDictionary(0); (1)
        String docsId = docsProp.getString("id");
        String docsName = docsProp.getString("Name");
        String docsType = docsProp.getString("Type");
        String docsCity = docsProp.getString("City");

        // Alternatively, access results value dictionary directly
        final Hotel hotel = new Hotel();
        hotel.setId(result.getDictionary(0).getString("id")); (2)
        hotel.setType(result.getDictionary(0).getString("Type"));
        hotel.setName(result.getDictionary(0).getString("Name"));
        hotel.setCity(result.getDictionary(0).getString("City"));
        hotel.setCountry(result.getDictionary(0).getString("Country"));
        hotel.setDescription(result.getDictionary(0).getString("Description"));
        hotels.put(hotel.getId(), hotel);
    }
}

1	The dictionary of document properties using the database name as the key. You can add this dictionary to an array of returned matches, for processing elsewhere in the app.
2	Alternatively you can access the document properties here, by using the property names as keys to the dictionary object.

Select Specific Properties

Query

Here we use SelectResult.expression(property("<property-name>"))) to specify the document properties we want our query to return — see: Example 18.

Example 18. Query selecting Specific Properties

Kotlin
Java

Kotlin
ViewCopy
val query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id),
        SelectResult.property("country"),
        SelectResult.property("name")
    )
    .from(DataSource.collection(collection))

Java
ViewCopy

Query listQuery =
    QueryBuilder.select(
            SelectResult.expression(Meta.id),
            SelectResult.property("name"),
            SelectResult.property("Name"),
            SelectResult.property("Type"),
            SelectResult.property("City"))
        .from(DataSource.collection(collection));

Result Set Format

The result set returned when selecting only specific document properties is an array of dictionary objects — one for each document matching the query criteria.

Each result object comprises a key-value pair for each selected document property — see Example 19

Example 19. Format of Result Set (Specific Properties)

json
Copy
[
  { (1)
    "id": "hotel123",
    "type": "hotel",
    "name": "Hotel Ghia"
  },
  { (2)
    "id": "hotel456",
    "type": "hotel",
    "name": "Hotel Deluxe",
  }
]

1	The result for the first document matching the query criteria.
2	The result for the next document matching the query criteria.

Result Set Access

Access the retrieved properties by converting each row into a dictionary — as shown in Example 20.

Example 20. Using Returned Document Properties (Specific Properties)

Kotlin
Java

Kotlin
ViewCopy
query.execute().use { rs ->
    rs.allResults().forEach {
        log("Hotel name -> ${it.getString("name")}, in ${it.getString("country")}")
    }
}

Java
ViewCopy
HashMap<String, Hotel> hotels = new HashMap<>();
try (ResultSet resultSet = listQuery.execute()) {
    for (Result result: resultSet) {

        // get data direct from result k-v pairs
        final Hotel hotel = new Hotel();
        hotel.setId(result.getString("id"));
        hotel.setType(result.getString("Type"));
        hotel.setName(result.getString("Name"));
        hotel.setCity(result.getString("City"));

        // Store created hotel object in a hashmap of hotels
        hotels.put(hotel.getId(), hotel);

        // Get result k-v pairs into a 'dictionary' object
        Map<String, Object> thisDocsProps = result.toMap();
        String docId =
            thisDocsProps.getOrDefault("id", null).toString();
        String docName =
            thisDocsProps.getOrDefault("Name", null).toString();
        String docType =
            thisDocsProps.getOrDefault("Type", null).toString();
        String docCity =
            thisDocsProps.getOrDefault("City", null).toString();
    }
}

Select Document Id Only

Query

You would typically use this type of query if retrieval of document properties directly would consume excessive amounts of memory and-or processing time — see: Example 21.

Example 21. Query selecting only Doc Id

Kotlin
Java

Kotlin
ViewCopy
val query = QueryBuilder
    .select(
        SelectResult.expression(Meta.id).`as`("hotelId")
    )
    .from(DataSource.collection(collection))

Java
ViewCopy
Query listQuery =
    QueryBuilder.select(SelectResult.expression(Meta.id).as("metaID"))
        .from(DataSource.collection(collection));

Result Set Format

The result set returned by queries using a SelectResult expression of the form SelectResult.expression(meta.id) is an array of dictionary objects — one for each document matching the query criteria. Each result object has id as the key and the ID value as its value — -see Example 22.

Example 22. Format of Result Set (Doc Id only)

json
Copy
[
  {
    "id": "hotel123"
  },
  {
    "id": "hotel456"
  },
]

Result Set Access

In this case, access the required document’s properties by unpacking the id and using it to get the document from the database — see: Example 23.

Example 23. Using Returned Document Properties (Document Id)

Kotlin
Java

Kotlin
ViewCopy
query.execute().use { rs ->
    rs.allResults().forEach {
        log("hotel id ->${it.getString("hotelId")}")
    }
}

Java
ViewCopy
try (ResultSet rs = listQuery.execute()) {
    for (Result result: rs.allResults()) {

        // get the ID form the result's k-v pair array
        String thisDocsId = result.getString("metaID"); (1)

        // Get document from DB using retrieved ID
        Document thisDoc = collection.getDocument(thisDocsId);

        // Process document as required
        String thisDocsName = thisDoc.getString("Name");
    }
}

1	Extract the Id value from the dictionary and use it to get the document from the database

Select Count-only

Query

Example 24. Query selecting a Count-only

Kotlin
Java

Kotlin
ViewCopy

val query = QueryBuilder
    .select(
        SelectResult.expression(Function.count(Expression.string("*"))).`as`("mycount")
    ) (1)
    .from(DataSource.collection(collection))

Java
ViewCopy
Query listQuery = QueryBuilder.select(
        SelectResult.expression(Function.count(Expression.string("*"))).as("mycount")) (1)
    .from(DataSource.collection(collection));

1	The alias name, `mycount`, is used to access the count value.

Result Set Format

The result set returned by a count such as Select.expression(Function.count(Expression.all))) is a key-value pair. The key is the count name, as defined using SelectResult.as — see: Example 25 for the format and Example 24 for the query.

Example 25. Format of Result Set (Count)

json
Copy
{
  "mycount": 6
}

1	The key-value pair returned by a count.

Result Set Access

Access the count using its alias name (mycount in this example) — see Example 26

Example 26. Using Returned Document Properties (Count)

Kotlin
Java

Kotlin
ViewCopy
query.execute().use { rs ->
    rs.allResults().forEach {
        log("name -> ${it.getInt("mycount")}")
    }
}

Java
ViewCopy
try (ResultSet resultSet = listQuery.execute()) {
    for (Result result: resultSet) {

        // Retrieve count using key 'mycount'
        Integer altDocId = result.getInt("mycount");

        // Alternatively, use the index
        Integer orDocId = result.getInt(0);
    }
}

// Or even leave out the for-loop altogether
int resultCount;
try (ResultSet resultSet = listQuery.execute()) {
    resultCount = resultSet.next().getInt("mycount");
}

1	Get the count using the `SelectResult.as` alias, which is used as its key.

Handling Pagination

One way to handle pagination in high-volume queries is to retrieve the results in batches. Use the limit and offset feature, to return a defined number of results starting from a given offset — see: Example 27.

Example 27. Query Pagination

Kotlin
Java

Kotlin
ViewCopy
val thisOffset = 0
val thisLimit = 20
val listQuery = QueryBuilder
    .select(SelectResult.all())
    .from(DataSource.collection(collection))
    .limit(
        Expression.intValue(thisLimit),
        Expression.intValue(thisOffset)
    ) (1)

Java
ViewCopy

int thisOffset = 0;
int thisLimit = 20;

Query listQuery =
    QueryBuilder
        .select(SelectResult.all())
        .from(DataSource.collection(collection))
        .limit(
            Expression.intValue(thisLimit),
            Expression.intValue(thisOffset)); (1)

1	Return a maximum of `limit` results starting from result number `offset`

For more on using the QueryBuilder API, see our blog: Introducing the Query Interface in Couchbase Mobile

JSON Result Sets

Couchbase Lite for Android provides a convenience API to convert query results to JSON strings.

Example 28. Using JSON Results

Use Result.toJSON() to transform your result string into a JSON string, which can easily be serialized or used as required in your application. See <> for a working example.

Kotlin
Java

Kotlin
ViewCopy
// Uses Jackson JSON processor
val mapper = ObjectMapper()
val hotels = mutableListOf<Hotel>()

listQuery.execute().use { rs ->
    rs.forEach {

        // Get result as JSON string
        val json = it.toJSON() (1)

        // Get Hashmap from JSON string
        val dictFromJSONstring = mapper.readValue(json, HashMap::class.java) (2)

        // Use created hashmap
        val hotelId = dictFromJSONstring["id"].toString() //
        val hotelType = dictFromJSONstring["type"].toString()
        val hotelname = dictFromJSONstring["name"].toString()

        // Get custom object from JSON string
        val thisHotel = mapper.readValue(json, Hotel::class.java) (3)
        hotels.add(thisHotel)
    }
}

Java
ViewCopy
// Uses Jackson JSON processor
ObjectMapper mapper = new ObjectMapper();
List<Hotel> hotels = new ArrayList<>();

try (ResultSet rs = listQuery.execute()) {
    for (Result result: rs) {
        String json = result.toJSON();
        Map<String, String> dictFromJSONstring = mapper.readValue(json, HashMap.class);

        String hotelId = dictFromJSONstring.get("id");
        String hotelType = dictFromJSONstring.get("type");
        String hotelname = dictFromJSONstring.get("name");

        // Get custom object from JSON string
        Hotel thisHotel = mapper.readValue(json, Hotel.class);
        hotels.add(thisHotel);
    }
}

JSON String Format

If your query selects ALL then the JSON format will be:

JSON
Copy
{
  database-name: {
    key1: "value1",
    keyx: "valuex"
  }
}

If your query selects a sub-set of available properties then the JSON format will be:

JSON
Copy
{
  key1: "value1",
  keyx: "valuex"
}

Predictive Query

Enterprise Edition only

Predictive Query is an Enterprise Edition feature.

Predictive Query enables Couchbase Lite queries to use machine learning, by providing query functions that can process document data (properties or blobs) via trained ML models.

Let’s consider an image classifier model that takes a picture as input and outputs a label and probability.

To run a predictive query with a model as the one shown above, you must implement the following steps.

Integrate the Model
Register the Model
Create an Index (Optional)
Run a Prediction Query
Deregister the Model

Integrate the Model

To integrate a model with Couchbase Lite, you must implement the PredictiveModel interface which has only one function called predict() — see: Example 29.

Example 29. Integrating a predictive model

Kotlin
Java

Kotlin
ViewCopy
// tensorFlowModel is a fake implementation
object TensorFlowModel {
    fun predictImage(data: ByteArray?): Map<String, Any?> = TODO()
}

object ImageClassifierModel : PredictiveModel {
    const val NAME = "ImageClassifier"

    // this would be the implementation of the ml model you have chosen
    override fun predict(input: Dictionary) = input.getBlob("photo")?.let {
        MutableDictionary(TensorFlowModel.predictImage(it.content)) (1)
    }
}

Java
ViewCopy
class ImageClassifierModel implements PredictiveModel {
    @Override
    public Dictionary predict(@NonNull Dictionary input) {
        Blob blob = input.getBlob("photo");
        if (blob == null) { return null; }

        // tensorFlowModel is a fake implementation
        // this would be the implementation of the ml model you have chosen
        return new MutableDictionary(TensorFlowModel.predictImage(blob.getContent())); (1)
    }
}

@SuppressWarnings("unused")
class LogTestLogger implements com.couchbase.lite.Logger {
    @NonNull
    private final LogLevel level;

    public LogTestLogger(@NonNull LogLevel level) { this.level = level; }

    @NonNull
    @Override
    public LogLevel getLevel() { return level; }

    @Override
    public void log(@NonNull LogLevel level, @NonNull LogDomain domain, @NonNull String message) {

    }
}


@SuppressWarnings("unused")
class TensorFlowModel {
    public static Map<String, Object> predictImage(byte[] data) {
        return null;
    }
}
    // tensorFlowModel is a fake implementation
    // this would be the implementation of the ml model you have chosen
    public static class TensorFlowModel {
        public static Map<String, Object> predictImage(byte[] data) {
            return null;
        }
    }

    public static class ImageClassifierModel implements PredictiveModel {
        @Override
        public Dictionary predict(@NonNull Dictionary input) {
            Blob blob = input.getBlob("photo");

            // tensorFlowModel is a fake implementation
            // this would be the implementation of the ml model you have chosen
            return (blob == null)
                ? null
                : new MutableDictionary(TensorFlowModel.predictImage(blob.getContent())); (1)
        }
    }

1	The `predict(input) -> output` method provides the input and expects the result of using the machine learning model. The input and output of the predictive model is a `DictionaryObject`. Therefore, the supported data type will be constrained by the data type that the `DictionaryObject` supports.

Register the Model

To register the model you must create a new instance and pass it to the Database.prediction.registerModel static method.

Example 30. Registering a predictive model

Kotlin
Java

Kotlin
ViewCopy
Database.prediction.registerModel("ImageClassifier", ImageClassifierModel)

Java
ViewCopy
Database.prediction.registerModel("ImageClassifier", new ImageClassifierModel());

Create an Index

Creating an index for a predictive query is highly recommended. By computing the predictions during writes and building a prediction index, you can significantly improve the speed of prediction queries (which would otherwise have to be computed during reads).

There are two types of indexes for predictive queries:

Value Index
Predictive Index

Value Index

The code below creates a value index from the "label" value of the prediction result. When documents are added or updated, the index will call the prediction function to update the label value in the index.

Example 31. Creating a value index

Kotlin
Java

Kotlin
ViewCopy
collection.createIndex(
    "value-index-image-classifier",
    IndexBuilder.valueIndex(ValueIndexItem.expression(Expression.property("label")))
)

Java
ViewCopy
collection.createIndex(
    "value-index-image-classifier",
    IndexBuilder.valueIndex(ValueIndexItem.expression(Expression.property("label"))));

Predictive Index

Predictive Index is a new index type used for predictive query. It differs from the value index in that it caches the predictive results and creates a value index from that cache when the predictive results values are specified.

Example 32. Creating a predictive index

Here we create a predictive index from the label value of the prediction result.

Kotlin
Java

Kotlin
ViewCopy
val inputMap: Map<String, Any?> = mutableMapOf("numbers" to Expression.property("photo"))
collection.createIndex(
    "predictive-index-image-classifier",
    IndexBuilder.predictiveIndex("ImageClassifier", Expression.map(inputMap), null)
)

Java
ViewCopy
Map<String, Object> inputMap = new HashMap<>();
inputMap.put("numbers", Expression.property("photo"));
Expression input = Expression.map(inputMap);

PredictiveIndex index = IndexBuilder.predictiveIndex("ImageClassifier", input, null);
collection.createIndex("predictive-index-image-classifier", index);

Run a Prediction Query

The code below creates a query that calls the prediction function to return the "label" value for the first 10 results in the database.

Example 33. Creating a value index

Kotlin
Java

Kotlin
ViewCopy
val inputMap: Map<String, Any?> = mutableMapOf("photo" to Expression.property("photo"))
val prediction: PredictionFunction = Function.prediction(
    ImageClassifierModel.NAME,
    Expression.map(inputMap) (1)
)

val query = QueryBuilder
    .select(SelectResult.all())
    .from(DataSource.collection(collection))
    .where(
        prediction.propertyPath("label").equalTo(Expression.string("car"))
            .and(
                prediction.propertyPath("probability")
                    .greaterThanOrEqualTo(Expression.doubleValue(0.8))
            )
    )

query.execute().use {
    log("Number of rows: ${it.allResults().size}")
}

Java
ViewCopy
Map<String, Object> inputProperties = new HashMap<>();
inputProperties.put("photo", Expression.property("photo"));
Expression input = Expression.map(inputProperties);
PredictionFunction prediction = Function.prediction("ImageClassifier", input); (1)

Query query = QueryBuilder
    .select(SelectResult.all())
    .from(DataSource.collection(collection))
    .where(Expression.property("label").equalTo(Expression.string("car"))
        .and(prediction.propertyPath("probability").greaterThanOrEqualTo(Expression.doubleValue(0.8))));

// Run the query.
try (ResultSet result = query.execute()) {
    Logger.log("Number of rows: " + result.allResults().size());
}

The PredictiveModel.predict() method returns a constructed Prediction Function object which can be used further to specify a property value extracted from the output dictionary of the PredictiveModel.predict() function.

The null value returned by the prediction method will be interpreted as MISSING value in queries.

Deregister the Model

To deregister the model you must call the Database.prediction.unregisterModel static method.

Example 34. Deregister a value index

Kotlin
Java

Kotlin
ViewCopy
Database.prediction.unregisterModel("ImageClassifier")

Java
ViewCopy
Database.prediction.unregisterModel("ImageClassifier");

How to . . .

Learn more . . .

Dive Deeper . . .

Mobile Forum | Blog | Tutorials

1. Starting in Couchbase Lite 2.5