Working with N1QL

Working with N1QL

You can query a bucket using the N1QL query language.

Note: N1QL is currently experimental and may change in subsequent versions.

To query via N1QL, you need to make sure to have the query engine downloaded, started and pointed to your cluster. For more information about using N1QL, see http://query.couchbase.com .

Next, you need to enable N1QL either through a system property or through the environment builder. This step will go away once N1QL is fully integrated into the server:

To enable N1QL by using a system property:

// Through a system property
System.setProperty("com.couchbase.queryEnabled", "true");

Cluster cluster = CouchbaseCluster.create();
Bucket bucket = cluster.openBucket("beer-sample");

To enable N1QL by using the builder:

// Through the builder
Cluster cluster = CouchbaseCluster.create(DefaultCouchbaseEnvironment
    .builder()
    .queryEnabled(true)
    .build());

Bucket bucket = cluster.openBucket("beer-sample");

Finally, you are ready to run the various kinds of queries. All queries are composed at a minimum of a statement and optionally of N1QL additional parameters. The simplest form of query is just a raw string statement, or a domain-specific language (DSL) statement:

// raw string query
QueryResult queryResult =
   bucket.query(Query.simple("SELECT * FROM beer-sample LIMIT 10"));

// using the DSL
QueryResult query = bucket.query(select("*").from("beer-sample").limit(10));

The DSL provides a type-safe and intuitive way to perform queries. The select() method is a static import that kicks off a BNF-aware DSL for N1QL. (BNF stands for Backus-Naur Form.)

The query always returns a QueryResult , which aside from the actual QueryRow s also contains debug or error information if supplied.

  • parseSuccess : Returns true if the query could correctly be parsed. 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, can be an empty list.
  • rows : Same as allRows but in an iterator form (the QueryResult 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. This can be useful to group several queries (eg. in a kind of in-house transaction) and find all associated queries.
  • info : Returns a JsonObject containing metrics for the query (like number of results, processing time, etc...).
  • errors : Returns a list of JsonObject describing errors or warnings (if any occurred during execution).

Querying Asynchronously

Querying asynchronously is done through the AsyncBucket interface, obtained 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("beer-sample").limit(10))
.subscribe(result -> {
    result.errors()
        .subscribe(
            e -> System.err.println("N1QL Error/Warning: " + e),
            runtimeError -> runtimeError.printStackTrace()
        );
    result.rows()
        .map(row -> row.value())
        .subscribe(
            rowContent -> System.out.println(rowContent),
            runtimeError -> runtimeError.printStackTrace()
        )
    }
);

First, we see that we work on asynchronous mode. Second line issues a Statement using the DSL. Then we subscribe a first time to trigger the query and obtain the result.

On receiving the result (there will be only one), we subscribe to its components to be notified respectively of n1ql errors and n1ql results. Results (as rows) are first mapped into their JSON values.

Notice we also define an onError behavior for both n1ql errors stream and results stream, that just prints the stack trace.

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

Different Kinds of Queries

Queries are represented by the Query interface, which also provides factory methods to create all variants of queries. Each variant can be constructed from a Statement , even in String form, but also additionally can define a QueryParams . This represents additional N1QL parameters, like setting a clientContextId .

Variants of queries are:
  • simple : The basic query used so far in this tutorial.
  • parameterized : A variant to use with statements containing placeholders (like $1 or $placeholder ). The user must provide the values for the placeholders as well, either as a JsonArray (for positional parameters) or JsonObject (for named parameters).
  • prepared : This is very much like the idea of an SQL prepared statement. First the bucket.prepare(statement) method must be called to obtain a QueryPlan . This plan can then be cached and replayed using Query.prepared(plan) , skipping the query parsing and preparation on the server side. Prepared statements can also use placeholders.

Reading Your Own Writes (RYOW)

Often you will want to insert or update some data into Couchbase and immediately read this data back using a N1QL query. This is referred to as RYOW (Read Your Own Write).

This can be achieved by using the QueryParams , and more precisely the consistency parameter:


JsonDocument doc = JsonDocument.create("test", JsonObject.create().put("test", true));
bucket.insert(doc);

Statement select = select("*").from("beer-sample")
    .where(x("test").eq(true));
QueryParams ryow = QueryParams.build().consistency(ScanConsistency.REQUEST_PLUS);

bucket.async()
    .query(Query.simple(select, ryow))
    .subscribe(result -> {
    result.errors()
        .subscribe(
            e -> System.err.println("N1QL Error/Warning: " + e),
            runtimeError -> runtimeError.printStackTrace()
        );
    result.rows()
        .map(row -> row.value())
        .subscribe(
            rowContent -> System.out.println(rowContent),
            runtimeError -> runtimeError.printStackTrace()
        )
    }
);