Query

Overview

Couchbase Lite includes support for a N1QL like query interface ^[1]. Database can be queried by constructing a query using a Query builder and then executing that query.

The Query interface in Couchbase Lite is powerful and includes support for the following among others

Pattern Matching
Regex Matching
Math Functions
String Manipulation Functions
Aggregate Functions
Grouping
Joins (within single database)
Sorting
NilOrMissing properties

Simple Query

The travel app has many instances of querying the database. We will discuss a simple example here.

Open the file FlightsDao.java. We will review the searchAirportsAsync(@Nonnull String prefix, int maxResults) method.

FlightsDao.java

@Nonnull
    private List<String> searchAirportsAsync(@Nonnull String prefix, int maxResults) throws CouchbaseLiteException {
  ...
}

The query below selects the "name" property in documents from the database where the type property is equal to airport and the "airportname" property is equal to the search term. The query is executed using the execute() method.

  final String target = "%" + prefix + "%";
  final ResultSet results = QueryBuilder.select(SelectResult
      .expression(Expression.property(PROP_AIRPORT_NAME)))
      .from(DataSource.database(db.getDatabase()))
      .where(Expression.property(DbManager.PROP_DOC_TYPE).equalTo(Expression.string(TYPE_AIRPORT))
          .and(Function.lower(Expression.property(PROP_AIRPORT_NAME))
              .like(Function.lower(Expression.string(target))))
          .or(Function.lower(Expression.property(PROP_FAA))
              .like(Function.lower(Expression.string(target)))))
      .orderBy(Ordering.property(PROP_AIRPORT_NAME).ascending())
      .limit(Expression.intValue(maxResults))
      .execute();

Each row in the result will contain a single property called "airportname".

final List<String> airports = new ArrayList<>();
for (Result result : results.allResults()) {
    final String airportName = result.getString(PROP_AIRPORT_NAME);
    if (airportName != null) { airports.add(airportName); }
}

Try it out

Log into the Travel Sample Mobile app as "demo" user and password as "password"
Click the "FLIGHTS" button to make a flight reservation
In the "From" airport textfield, enter "Detroit"
Verify that the first item in the drop down list is "Detroit Metro Wayne Co"

Advanced Query

In this section we will discuss the JOIN query; intra-database joins.

If you recall from the Data Modeling section, the document with a type equal to "bookmarkedhotels" contains a hotels property which is an array of IDs of bookmarked hotels.

{
  "_id": "hotel1",
  "name": "San Francisco Hotel",
  "address": "123, Park Street, San Francisco"
}

{
  "type": "bookmarkedhotels",
  "hotels": ["hotel1", "hotel2"]
}

We will review the query that fetches documents whose \_id is included in the "hotels" property array of the document of type "bookmarkedhotels".

Open the fileBookmarkDao.java. We will review the queryBookmarksAsync() method.

BookmarkDao.java

  @Nonnull
  private List<Hotel> queryBookmarksAsync() throws CouchbaseLiteException {
    ...
  }

The query object uses a join expression to find all the hotel document referenced in the "hotels" array of the bookmark document.

  final ResultSet results = QueryBuilder
      .select(SelectResult.all().from("bookmark"), SelectResult.all().from("hotel"))
      .from(DataSource.database(database).as("bookmark"))
      .join(Join.join(DataSource.database(database).as("hotel"))
          .on(ArrayFunction.contains(Expression.property(PROP_BOOKMARKS)
              .from("bookmark"), Meta.id.from("hotel"))))
      .where(Expression.property(DbManager.PROP_DOC_TYPE).from("bookmark")
          .equalTo(Expression.string(DbManager.DOC_TYPE_HOTEL_BOOKMARKS)))
      .execute();

We use the execute() method to get the results back

 for (Result result : results) {
            final Hotel hotel = Hotel.fromDictionary(result.getDictionary(1));
            if (hotel != null) { bookmarks.add(hotel); }
        }

Try it out

Log into the Travel Sample Mobile app as "Guest" user by selecting "Proceed as Guest"
Click on "ADD"" button
In the "Location" text field, enter "London"
In the "Description" text field, enter "Pets"
Verify that you see the "Novotel London West" listed
Select the entry by clicking on it
Click "DONE" button to bookmark
Verify that the Novatel hotel shows up in the list on the "Bookmarks" page — see Figure 1

Figure 1. Advanced Query

1. From 2.0