Query

Overview

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

The Query interface in Couchbase Lite 2.0 is poweful 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.

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

    java simple query

Advanced Query

In this section we will discuss the JOIN query. JOIN Queries in Couchbase Lite 2.0 are 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.

  @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

    java advanced query