A newer version of this documentation is available.

Querying with N1QL

Parallel data management for complex queries over many records, using a familiar SQL-like syntax.

Unresolved include directive in modules/concept-docs/pages/n1ql-query.adoc - include::7.1@sdk:shared:partial$n1ql-queries.adoc[]

Unresolved include directive in modules/concept-docs/pages/n1ql-query.adoc - include::7.1@sdk:shared:partial$n1ql-queries.adoc[] The maximum client-side query cache size is 5000 entries.

QueryResult result = cluster.query(
    "select count(*) from `travel-sample`.inventory.airport where country = ?",
    QueryOptions.queryOptions().adhoc(false).parameters(JsonArray.from("France"))
);

Indexes

The Couchbase Query Service makes use of indexes in order to do its work. Indexes replicate subsets of documents from data nodes over to index nodes, allowing specific data (for example, specific document properties) to be retrieved quickly, and to distribute load away from data nodes in MDS topologies.

In order to make a bucket queryable, it must have at least one index defined.

You can define a primary index on a bucket. When a primary index is defined you can issue non-covered (see below) queries on the bucket as well. This includes using the META function in the queries.

CREATE PRIMARY INDEX ON `users`

You can also define indexes over given document fields and then use those fields in the query:

CREATE INDEX ix_name ON `users`(name);
CREATE INDEX ix_email ON `users`(email);

This would allow you to query the users bucket regarding a document’s name or email properties, thus:

SELECT name, email FROM `users` WHERE name="Monty Python" OR email="monty@python.org";

Indexes help improve the performance of a query. When an index includes the actual values of all the fields specified in the query, the index covers the query, and eliminates the need to fetch the actual values from the Data Service. An index, in this case, is called a covering index, and the query is called a covered query. For more information, see Covering Indexes.

You can also create and define indexes in the SDK using:

QueryIndexManager indexManager = cluster.queryIndexes();

indexManager.createPrimaryIndex(bucketName);
indexManager.createIndex(bucketName, "ix_name", Collections.singletonList("name"));
indexManager.createIndex(bucketName, "ix_email", Collections.singletonList("preferred_email"));

Index Building

Creating indexes on buckets with many existing documents can take a long time. You can build indexes in the background, creating deferred indexes. The deferred indexes can be built together, rather than having to re-scan the entire bucket for each index.

CREATE PRIMARY INDEX ON `users` WITH {"defer_build": true};
CREATE INDEX ix_name ON `users`(name) WITH {"defer_build": true};
CREATE INDEX ix_email ON `users`(email) WITH {"defer_build": true};
BUILD INDEX ON `users`(`#primary`, `ix_name`, `ix_email`);

The indexes are not built until the BUILD INDEX statement is executed. At this point, the server scans all of the documents in the users bucket, and indexes it for all of the applicable indexes (in this case, those that have a name or email field).

Building deferred indexes can also be done via the SDK:

QueryIndexManager indexManager = cluster.queryIndexes();

indexManager.createPrimaryIndex(bucketName,
    CreatePrimaryQueryIndexOptions.createPrimaryQueryIndexOptions().deferred(true));
indexManager.createIndex(bucketName, "ix_name", Collections.singletonList("name"),
    CreateQueryIndexOptions.createQueryIndexOptions().deferred(true));
indexManager.createIndex(bucketName, "ix_email", Collections.singletonList("preferred_email"),
    CreateQueryIndexOptions.createQueryIndexOptions().deferred(true));
indexManager.buildDeferredIndexes(bucketName);
indexManager.watchIndexes(bucketName, Arrays.asList("ix_name", "ix_email"), Duration.ofMinutes(5));

Unresolved include directive in modules/concept-docs/pages/n1ql-query.adoc - include::7.1@sdk:shared:partial$n1ql-queries.adoc[]

The following options are available:

Unresolved include directive in modules/concept-docs/pages/n1ql-query.adoc - include::7.1@server:learn:page$services-and-indexes/indexes/index-replication.adoc[]

Consider the following snippet:

String id = "user::" + UUID.randomUUID();
collection.insert(
    id,
    JsonObject.create().put("value", true)
);

cluster.query(
    "select * from `" + bucketName + "`.inventory.airport where META().id = $id",
    QueryOptions.queryOptions()
        .parameters(JsonObject.create().put("id", id))
);

The above query may not return the newly inserted document because it has not yet been indexed. The query is issued immediately after document creation, and in this case the Query Engine may process the query before the index has been updated.

If the above code is modified to use REQUEST_PLUS, query processing will wait until all updates have been processed and recalculated into the index from the point in time the query was received:

cluster.query(
    "select * from `" + bucketName + "`.inventory.airport where META().id = $id",
    QueryOptions.queryOptions()
        .parameters(JsonObject.create().put("id", id))
        .scanConsistency(QueryScanConsistency.REQUEST_PLUS)
);

This gives the application developer more control over the balance between performance (latency) and consistency, and allows optimization on a case-by-case basis.

Collections and Scopes, and the Query Context

From Couchbase Server release 7.0 the Collections feature lets you logically group similar documents into Collections.

You can query collections in N1QL, by referring to a fully qualified keyspace. For example, to list the documents in the airline collection in the inventory scope:

SELECT * FROM `travel-sample`.inventory.airline;

As a convenience, you can also query a partial keyspace from the Query Context of a specific Scope. For example, from the context of `travel-sample`.inventory, you could abbreviate the previous query to:

SELECT * FROM airline;

To do this, you can Set a Query Context in the Query Workbench or query at scope level using the SDK.