N1QL from the SDK
Edit this article in GitHub
Version 2.3

N1QL from the SDK

You can perform N1QL queries via the Java client.

Note: See Couchbase Developer documentation for a quick intro to N1QL.
To issue N1QL queries, you should create a N1qlQuery object, and pass it to the query(N1qlQuery q) method in the Bucket class. A few variants of such a query exist:
  • Simple queries, which are only strings and do not use placeholders;
  • Parameterized queries, which use numbered or named placeholders.
You can create each via the corresponding factory method on N1qlQuery, which you can create using a Statement produced by the N1QL DSL or using the statement directly as a String.

The return value from query() is the object N1qlQueryResult. Iterating over the object will yield the rows returned by the server for the given query (as N1qlQueryRow). Each row represents a row received for the query.

Additionally, you can get the List<N1qlQueryRow> from the allRows() method on the result. Note that errors returned by the N1QL service during execution are represented as JsonObject accessible through the errors() method, rather than exceptions. Here is the complete list of N1qlQueryResult methods:

  • parseSuccess: Returns true if the query could be parsed correctly. This information is available immediately even if the actual list of results takes more time to be computed and streamed to the client.
  • finalSuccess: Returns true if the whole query could be executed successfully, including retrieving all the results and streaming them to the client.
  • allRows: Contains all rows returned by the query; it can be an empty list.
  • rows: Same as allRows but in an iterator form (the N1qlQueryResult itself is iterable).
  • requestId: The server-generated unique ID for this query (useful to find associated logs on the N1QL server).
  • clientContextId: User-provided identifier reflected in the server's response. The identifier can be useful for grouping several queries (for example, in a kind of in-house transaction) and find all associated queries.
  • info: Returns a N1qlMetrics object, which contains metrics for the query (such as the number of results or processing time).
  • errors: Returns a list of JsonObject describing errors or warnings (if any occurred during execution).

Each N1qlQueryRow exposes the JSON representation of the value() as a JsonObject. It also gives the raw bytes for said value (byteValue()), in case you need them to apply your deserialization or SELECT RAW is used which cannot be turned into a JsonObject.

You can use N1QL placeholders in the query. Placeholders allow you to specify variable constraints for an otherwise constant query. To use placeholders, manually construct a N1QLQuery object with the base query string, and use a JsonObject of names -> keyword or a JsonArray of positional arguments for named or positional placeholders, respectively:
N1QL Query with placeholders
import static com.couchbase.client.java.query.Select.select;
import static com.couchbase.client.java.query.dsl.Expression.*;

// ...
Statement statement = select("fname", "lname", "age").from(i("default")).where(x("age").gt(x("$age")));
JsonObject placeholderValues = JsonObject.create().put("age", 22);
q = N1qlQuery.parameterized(statement, placeholderValues);
for (N1qlQueryRow row : bkt.query(q)) {
    System.out.println(row);
}
N1QL queries can also be fine tuned by setting options using N1qlParams object. These parameters should be added to the query object during creation. Here is a subset of options that can be tuned:
  • serverSideTimeout - sets the maximum server side timeout for the query.
  • withContextId - adds client context ID to the request that will be sent back in the response, allowing clients to meaningfully trace requests/responses when many are exchanged.
  • consistency - sets the consistency level for the query.
  • maxParallelism - can override the max parallelism used by the server.
  • adhoc - allows to specify if this query is adhoc or not.
Adding N1QL parameters to Query
//Using N1qlParams to set adhoc as false for using prepared statements
N1qlParams params = N1qlParams.build().adhoc(false);
N1qlQuery query = N1qlQuery.simple("select count(*) from `mybucket`", params);
For more information on query parameters in N1QL, see N1QL REST API.

Building Statements with the DSL

The N1QL domain-specific language (DSL) is a powerful way to guide you in building your statements: you obtain type safety and autocompletion of relevant methods / N1QL clauses.

As of the 2.2.3 version of the SDK, the DSL supports the following features:
  • Data selection with Select.select(...) as an entry point (including index hinting).
  • All N1QL functions, organized by topic in the helper classes in the com.couchbase.client.java.query.dsl.functions package (eg. AggregateFunctions).
  • A mini DSL for building CASE constructs via factory methods on the Case class.
  • A mini DSL for building collection constructs (ANY, EVERY, ARRAY...) via factory methods on the Collections class.
  • Index management (index and primary index creation/deletion/deferred building) with Index factory methods as an entry point.
  • Data modification with Insert, Update, Upsert and Delete as entry points.

To construct statements, you can use the Expression class that comes in with a variety of factory methods to construct both tokens and literals programmatically. Expressions can be combined and chained with operators (like gte for "greater than or equal to" comparison).

Use x to construct a token, s to construct a string literal, i to construct a token escaped with backticks (for example, the bucket name beer-sample must be escaped because it contains a dash).

If you find out that the DSL doesn't support a particular statement, clause or keyword, you can always revert to providing your statement in plain String form. Using the String form ensures that even if the DSL lags behind the evolutions of the language, users will always have a mean of using new language features.

Querying Asynchronously

To perform a query asynchronously, use the AsyncBucket interface that you obtain by calling bucket.async(). The API is pretty similar except everything is returned as an Observable. Some of the components of the query result (an AsyncQueryResult) can also be delayed and so returned as Observables. Only requestId, clientContextId and parseSuccess return immediately.

The following Java 8 code prints the found documents or errors as they come:

bucket.async()
    .query(select("*").from(i("beer-sample")).limit(10))
    .flatMap(result ->
        result.errors()
        .flatMap(e -> Observable.<AsyncN1qlQueryRow>error(new CouchbaseException("N1QL Error/Warning: " + e)))
        .switchIfEmpty(result.rows())
    )
    .map(AsyncN1qlQueryRow::value)
    .subscribe(
        rowContent -> System.out.println(rowContent),
        runtimeError -> runtimeError.printStackTrace()
    );

First, you can see that asynchronous mode was used.

Second, line issues a Statement using the DSL (notice how "beer-sample" is escaped).

When receiving a result, first check if there are errors. If there are any, the first one is converted to a CouchbaseException that will be propagated in the Observable. If no errors are found (errors() is empty), switch to inspecting the rows, then map each received row to its JSON value.

Eventually, trigger the whole process by subscribing to the Observable you have built. When a row JSON is received, print it; when an error is propagated, print the stack trace.

Note: All this is done asynchronously so it's not suitable for a simple test (where the main thread would potentially exit before any result could have been displayed).

Conditionals, Case Expressions Mini DSL

The com.couchbase.client.java.query.dsl.functions.Case class contains a mini-DSL to deal with Conditional operators in N1QL of the CASE family.

The Simple CASE expression is defined as:

CASE expression  ( WHEN expression THEN expression)
[ ( WHEN expression THEN expression) ]*
[  ELSE expression ]  END

The Searched CASE expression is defined as:

CASE  ( WHEN  condition THEN expression)
[( WHEN  condition THEN expression ) ]*
[ ELSE  expression ] END 

The corresponding mini-DSL are Case.caseSimple and Case.caseSearch. Simple Case will compare the initial expression with each WHEN clause for equality, returning the corresponding THEN expression if a match is found. Search Case allows for a different condition for each WHEN clause.

Let's see two examples. The first one could be used to map match results to a score:

CASE hist.result WHEN "won" THEN 1 ELSE 0 END
//import static com.couchbase.client.java.query.dsl.Expression.*;
//import static com.couchbase.client.java.query.dsl.functions.Case.*;

caseSimple(x("hist.result"))
    .when(s("won")).then(x(1))
    .elseReturn(x(0))

The second example implements more complex scoring rule using a Search Case (first match of the day counts as 5 points if won):

CASE WHEN hist.result = "won" AND hist.matchNumber = 1 THEN 5
WHEN hist.result = "won" THEN 1
WHEN hist.result = "lost" THEN 0
END
//import static com.couchbase.client.java.query.dsl.Expression.*;
//import static com.couchbase.client.java.query.dsl.functions.Case.*;

caseSearch()
    .when(x("hist.result").eq(s("won")).and(x("hist.matchNumber").eq(1))).then(x(5))
    .when(x("hist.result").eq(s("won"))).then(x(1))
    .when(x("hist.result").eq(s("lost"))).then(x(0))
    .end(); //no ELSE clause means other values will return NULL, have to explicitly close the CASE

Collection Operators Mini DSL

The com.couchbase.client.java.query.dsl.functions.Collections class contains a mini-DSL to deal with Collections operators in N1QL, such as ANY, EVERY, ARRAY and FIRST.

For example, the ARRAY construct is defined as:

ARRAY expression FOR variable ( IN |  WITHIN ) expression
[ ,  variable ( IN | WITHIN ) expression ]* [ ( WHEN  condition) ] END 

The corresponding mini-DSL is Collections.arrayIn (or Collections.arrayWithin if you want to start with a WITHIN clause). Let's see two examples from the following statement, which extracts children and also lists the ones that are "teenagers":

SELECT tutorial.fname || ' ' || tutorial.lname AS adult,
    ARRAY child FOR child IN tutorial.children END AS children,
    ARRAY child.fname FOR child IN tutorial.children WHEN child.age >= 12 END AS teenagers
FROM tutorial WHERE tutorial.children IS NOT NULL;

Here is how to write the second and third lines using the DSL:

//import static com.couchbase.client.java.query.dsl.Expression.*;
//import static com.couchbase.client.java.query.dsl.functions.Collections.*;

//ARRAY child FOR child IN tutorial.children END AS children
arrayIn(x("child"), "child", path("tutorial", "children")).end().as("children");

//ARRAY child.fname FOR child IN tutorial.children WHEN child.age >= 12 END AS teenagers
arrayIn(path("child", "fname"), "child", path("tutorial", "children")).when(path("child", "age").gte(12)).as("teenagers"));

Similarly, ANY allows to test for a condition that applies to at least one member of a nested array (you can also match on EVERY member of the array). ANY is defined as:

ANY variable ( IN  | WITHIN ) expression
[  ,  variable ( IN | WITHIN ) expression  ]*
SATISFIES condition  END

In the previous example, you would see an entry for a parent that doesn't have "teenagers" (its "teenagers" field would be empty), because the statement didn't specify that the children should contain a "teenager". You can fix that with ANY, by rewriting the WHERE clause:

#...FROM tutorial
#replace "WHERE tutorial.children IS NOT NULL" with:
WHERE ANY child IN tutorial.children SATISFIES child.age >= 12;
//import static com.couchbase.client.java.query.dsl.Expression.*;
//import static com.couchbase.client.java.query.dsl.functions.Collections.*;

anyIn("child", x("tutorial.children")).satisfies(x("child.age").gte(12))