Working with Queries

    +

    Description — Couchbase Lite database data model concepts - queries
    Related Content — Predictive Queries | Live Queries | Indexes

    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"
    }
    Query query = QueryBuilder
        .select(
            SelectResult.expression(Meta.id),
            SelectResult.property("name"),
            SelectResult.property("type"))
        .from(DataSource.database(database))
        .where(Expression.property("type").equalTo(Expression.string("hotel")))
        .orderBy(Ordering.expression(Meta.id));
    
    try {
        ResultSet rs = query.execute();
        for (Result result : rs) {
            Log.i("Sample", String.format("hotel id -> %s", result.getString("id")));
            Log.i("Sample", String.format("hotel name -> %s", result.getString("name")));
        }
    } catch (CouchbaseLiteException e) {
        Log.e("Sample", e.getLocalizedMessage());
    }

    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.

    Query query = QueryBuilder
        .select(SelectResult.all())
        .from(DataSource.database(database))
        .where(Expression.property("type").equalTo(Expression.string("hotel")));
    [
        {
            "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 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".

    {
        "_id": "hotel123",
        "type": "hotel",
        "name": "Apple Droid"
    }
    Query query = QueryBuilder
        .select(SelectResult.all())
        .from(DataSource.database(database))
        .where(Expression.property("type").equalTo(Expression.string("hotel")))
        .limit(Expression.intValue(10));
    ResultSet rs = query.execute();
    for (Result result : rs) {
        Dictionary all = result.getDictionary(DATABASE_NAME);
        Log.i("Sample", String.format("name -> %s", all.getString("name")));
        Log.i("Sample", String.format("type -> %s", 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 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"]
    }
    Query 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"), Expression.string("Armani Langworth"))));
    ResultSet rs = query.execute();
    for (Result result : rs) {
        Log.i(
            "Sample",
            String.format("public_likes -> %s", 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".

    Expression[] values = new Expression[] {
        Expression.property("first"),
        Expression.property("last"),
        Expression.property("username")
    };
    
    Query query = 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.

    Query 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(Function.Expression.string("royal engineers museum")))));
    ResultSet rs = query.execute();
    for (Result result : rs) { Log.i("Sample", String.format("name -> %s", result.getString("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.

    Query 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%"))));
    ResultSet rs = query.execute();
    for (Result result : rs) { Log.i("Sample", String.format("name -> %s", 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 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.

    Query 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____r"))));
    ResultSet rs = query.execute();
    for (Result result : rs) { Log.i("Sample", String.format("name -> %s", result.getString("name"))); }

    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.

    Query 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")).regex(Expression.string("\\beng.*r\\b"))));
    ResultSet rs = query.execute();
    for (Result result : rs) { Log.i("Sample", String.format("name -> %s", result.getString("name"))); }

    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
    Where query = QueryBuilder
        .select(SelectResult.expression(Meta.id))
        .from(DataSource.database(database))
        .where(Meta.deleted);

    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.

    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.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"))));
    ResultSet rs = query.execute();
    for (Result result : rs) { Log.w("Sample", String.format("%s", 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.

    {
        "_id": "airport123",
        "type": "airport",
        "country": "United States",
        "geo": { "alt": 456 },
        "tz": "America/Anchorage"
    }
    Query query = QueryBuilder.select(
        SelectResult.expression(Function.count(Expression.string("*"))),
        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.intValue(300))))
        .groupBy(
            Expression.property("country"),
            Expression.property("tz"))
        .orderBy(Ordering.expression(Function.count(Expression.string("*"))).descending());
    ResultSet rs = query.execute();
    for (Result result : rs) {
        Log.i(
            "Sample",
            String.format(
                "There are %d airports on the %s timezone located in %s and above 300ft",
                result.getInt("$1"),
                result.getString("tz"),
                result.getString("country")));
    }
    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.

    Query query = QueryBuilder
        .select(
            SelectResult.expression(Meta.id),
            SelectResult.property("name"))
        .from(DataSource.database(database))
        .where(Expression.property("type").equalTo(Expression.string("hotel")))
        .orderBy(Ordering.property("name").ascending())
        .limit(Expression.intValue(10));
    ResultSet rs = query.execute();
    for (Result result : rs) { Log.i("Sample", String.format("%s", result.toMap())); }
    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.