QueryBuilder

Description — How to use QueryBuilder to build effective queries with Couchbase Lite for Dart

Related Content — SQL++ for Mobile | Live Queries | Indexes | Result Sets

Introduction

Couchbase Lite for Dart 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 query statements of the form shown in Example 1. The structure and semantics of the query format are based on that of Couchbase's SQL++ query language.

Example 1. Query Format

SELECT ____
FROM ____
JOIN ____
WHERE ____
GROUP BY ____
ORDER BY ____

The SELECT clause specifies the data to be returned by the query.
The FROM clause specifies the database to query the documents from.
The JOIN clause specifies the criteria for joining multiple documents.
The WHERE clause specifies the query criteria.
The SELECTed properties of documents matching this criteria will be returned in the result set.
The GROUP BY clause specifies the criteria used to group returned items in the result set.
The ORDER BY clause specifies the criteria used to order the items in the result set.

Indexing

tip

See the Indexing topic to learn more about indexing.

Before we begin querying documents, let's briefly mention the importance of having a query index. A query can only be fast if there's a pre-existing database index it can search to narrow down the set of documents to examine — see: Example 2, which shows how to create an index, and also the Query Troubleshooting topic.

note

Every index has to be updated whenever a document is updated. So many indexes could hurt write performance.

Good performance depends on designing and creating the right indexes to go along with your queries.

Example 2. Creating a New Index

This example creates a new index for the type and name properties in the Data Model.

final config = ValueIndexConfiguration(['type', 'name']);
await database.createIndex('TypeNameIndex', config);

Figure 1. Data Model

[
  {
    "id": "hotel123",
    "type": "hotel",
    "name": "Hotel Ghia"
  },
  {
    "id": "hotel456",
    "type": "hotel",
    "name": "Hotel Deluxe"
  }
]

SELECT Clause

Use the SELECT clause 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 the properties of selected documents - see Example 3.

Example 3. Using SELECT to Retrieve All Properties

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

final query = const QueryBuilder()
    .select(SelectResult.all())
    .from(DataSource.database(database).as('airline'));

The Query.execute() method returns each result as dictionary where they key is the database name, or the alias provided to the as function.

Example 4. ResultSet fFrmat From SelectResult.all()

[
  {
    "airline": {
      "callsign": "MILE-AIR",
      "country": "United States",
      "iata": "Q5",
      "icao": "MLA",
      "id": 10,
      "name": "40-Mile Air",
      "type": "airline"
    }
  },
  {
    "airline": {
      "callsign": "ALASKAN-AIR",
      "country": "United States",
      "iata": "AA",
      "icao": "AAA",
      "id": 10,
      "name": "Alaskan Airways",
      "type": "airline"
    }
  }
]

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 clause of your query — see: Example 5.

Example 5. Using SELECT to Retrieve Specific Properties

In this query we retrieve and then print the id, typeandname properties of each document.

final query = const QueryBuilder()
  .select(
    SelectResult.expression(Meta.id),
    SelectResult.property('type'),
    SelectResult.property('name'),
  )
  .from(DataSource.database(database));

final resultSet = await query.execute();

await for (final result in resultSet.asStream()) {
  print('id: ${result.string('id')}');
  print('name: ${result.string('name')}');
}

The Query.execute() method returns each result as a dictionary with one or more key-value pairs, one for each SelectResult expression, with the property-name as the key — see Example 6.

Example 6. Select Result Format

[
  {
    "id": "hotel123",
    "type": "hotel",
    "name": "Hotel Ghia"
  },
  {
    "id": "hotel456",
    "type": "hotel",
    "name": "Hotel Deluxe"
  }
]

See Result Sets for more on processing query results.

WHERE Clause

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

Comparison Operators

The expression comparators (see ExpressionInterface) can be used in the WHERE clause 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".

Example 7. Using WHERE

final query = const QueryBuilder()
  .select(SelectResult.all())
  .from(DataSource.database(database))
  .where(Expression.property('type').equalTo(Expression.string('hotel')))
  .limit(Expression.integer(10));

final resultSet = await query.execute();

await for (final result in resultSet.asStream()) {
  print('name: ${result.string('name')}');
}

Collection Operators

Array collection operators (see ArrayExpression) are useful to check if a given value is present in an array through the any, every and anyAndEvery operators.

CONTAINS Operator

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

Example 8. Using ArrayFunction.contains()

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

final query = const 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'),
        ),
      ),
  )
  .limit(Expression.integer(10));

final result = await query.execute();

await for (final result in resultSet.asStream()) {
  print('name: ${result.string('name')}');
}

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

Example 9. IN Operator

final query = const QueryBuilder()
  .select(SelectResult.all())
  .from(DataSource.database(database))
  .where(Expression.string('Armani').in_([
    Expression.property('first'),
    Expression.property('last'),
    Expression.property('username'),
  ]));

LIKE Operator

String matching

The LIKE operator can be used for string matching — see Example 10.

note

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 10. Like with Case-Insensitive Matching

final query = const 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.integer(10));

note

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 11 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" will match. Notice that the matches may span word boundaries.

Example 11. Wildcard Matches

final query = const 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.integer(10));

Wildcard Character Match

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

In Example 12 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 12. Wildcard Character Matching

final query = const 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___r')),
      ),
  )
  .limit(Expression.integer(10));

REGEX Operator

Similar to the wildcards in LIKE expressions, REGEX based pattern matching allows you to introduce an element of fuzziness in your search string — see the code shown in Example 13.

note

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

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

final query = const 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'))
          .regex(Expression.string('\\bEng.*e\\b')),
      ),
  )
  .limit(Expression.integer(10));

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

tip

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) as shown in Example 14.

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

final query = const QueryBuilder()
  .select(SelectResult.expression(Meta.id))
  .from(DataSource.database(database))
  .where(Meta.isDeleted);

JOIN Clause

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

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

final query = const 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 Clause

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.

Figure 2. Data Model for Example

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

Example 16. Using GROUP BY

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.

final query = const QueryBuilder()
  .select(
    SelectResult.expression(Function_.count(Expression.all())),
    SelectResult.expression(Expression.property('country')),
    SelectResult.expression(Expression.property('tz')),
  )
  .from(DataSource.database(database))
  .where(Expression.property('type').equalTo(Expression.string('airport')))
  .groupBy(
     Expression.property('country'),
     Expression.property('tz'),
  );

final resultSet = await query.execute();

await for (final result in resultSet.asStream()) {
  print(
    'There are ${result.integer(r'$1')} airports on '
    'the ${result.string('tz')} timezone located '
    'in ${result.string('country')} and above 300 ft.',
  );
}

ORDER BY Clause

It is possible to sort the results of a query based on a given expression result — see Example 17.

Example 17. Using ORDER BY

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

final query = const QueryBuilder()
  .select(
    SelectResult.expression(Meta.id),
    SelectResult.expression(Expression.property('title')),
  )
  .from(DataSource.database(database))
  .where(Expression.property('type').equalTo(Expression.string('hotel')))
  .orderBy(Ordering.property('title').ascending())
  .limit(Expression.integer(10));

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 QueryBuilder API includes four functions for date comparisons.

Function_.stringToMillis
The input to this must be a validly formatted ISO 8601 date string. Valid date strings must start with a date in the form YYYY-MM-DD (time only string are not supported). The end result will be an expression (with a numeric content) that can be further input into the query builder.
Function_.stringToUTC
The input to this must be a validly formatted ISO 8601 date string. Valid date strings must start with a date in the form YYYY-MM-DD (time only string are not supported). The end result will be an expression (with string content) that can be further input into the query builder.
Function_.millisToString
The input for this must be 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
The input for this must be 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.

QueryBuilder

Introduction​

Indexing​

SELECT Clause​

Return All Properties​

Return Selected Properties​

WHERE Clause​

Comparison Operators​

Collection Operators​

CONTAINS Operator​

IN Operator​

LIKE Operator​

String matching​

Wildcard Match​

Wildcard Character Match​

REGEX Operator​

Deleted Document​

JOIN Clause​

GROUP BY Clause​

ORDER BY Clause​

Date/Time Functions​

Introduction

Indexing

SELECT Clause

Return All Properties

Return Selected Properties

WHERE Clause

Comparison Operators

Collection Operators

CONTAINS Operator

IN Operator

LIKE Operator

String matching

Wildcard Match

Wildcard Character Match

REGEX Operator

Deleted Document

JOIN Clause

GROUP BY Clause

ORDER BY Clause

Date/Time Functions