Querying with N1QL

    +

    Unresolved include directive in modules/concept-docs/pages/n1ql-query.adoc - include::partial$attributes.adoc[]

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

    Unresolved directive in n1ql-query.adoc - include::{version-server}@sdk:shared:partial$n1ql-queries.adoc[tag=intro]

    Unresolved directive in n1ql-query.adoc - include::{version-server}@sdk:shared:partial$n1ql-queries.adoc[tag=prepared]

    For the Scala SDK, the adhoc parameter should be set to false for a plan to be prepared, or a prepared plan to be reused. Do not turn off the adhoc flag for every query to Server 6.0 and earlier, since only a finite number of query plans (currently 5000) can be stored in the SDK.

    val stmt =
      """select count(*)
      from `travel-sample`.inventory.airport
      where country=$1;"""
    val result = cluster.query(
      stmt,
      QueryOptions()
        .adhoc(false)
        .parameters(QueryParameters.Positional("United States"))
    )

    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 collection. 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 `travel-sample`.inventory.hotel(name);
    CREATE INDEX ix_email ON `travel-sample`.inventory.hotel(email);

    This would allow you to query the travel-sample bucket’s hotel collection regarding a document’s name or email properties, thus:

    SELECT name, email
    FROM `travel-sample`.inventory.hotel
    WHERE name="Glasgow Grand Central" OR email="grandcentralhotel@example.com";

    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:

    val result: Try[Unit] =
      cluster.queryIndexes.createPrimaryIndex("users")
    
    result match {
      case Success(_) =>
      case Failure(err) => println(s"Operation failed with err $err")
    }
    
    // From now on the examples will use `.get` rather than correct error handling,
    // for brevity
    cluster.queryIndexes.createIndex("users", "index_name", Seq("name")).get
    cluster.queryIndexes.createIndex("users", "index_email", Seq("email")).get

    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 `travel-sample`.inventory.hotel WITH {"defer_build": true};
    CREATE INDEX ix_name ON `travel-sample`.inventory.hotel(name) WITH {"defer_build": true};
    CREATE INDEX ix_email ON `travel-sample`.inventory.hotel(email) WITH {"defer_build": true};
    BUILD INDEX ON `travel-sample`.inventory.hotel(`#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:

    cluster.queryIndexes.createPrimaryIndex("users", deferred = Some(true))
    cluster.queryIndexes.createIndex("users", "index_name",
      Seq("name"), deferred = Some(true)).get
    cluster.queryIndexes.createIndex("users", "index_email",
      Seq("email"), deferred = Some(true)).get
    cluster.queryIndexes.buildDeferredIndexes("users")
    
    // Wait for the indexes to build
    cluster.queryIndexes.watchIndexes("users",
      Seq("index_name", "index_email", "#primary"),
      Duration("30 seconds")).get

    Note that the build step is still asynchronous, so watchIndexes is used to wait for the indexes to be completed. "#primary" is the primary index.

    Unresolved directive in n1ql-query.adoc - include::{version-server}@sdk:shared:partial$n1ql-queries.adoc[tag=index-consistency]

    The following options are available:

    Unresolved directive in n1ql-query.adoc - include::{version-server}@server:learn:page$services-and-indexes/indexes/index-replication.adoc[tag=scan_consistency]

    • A N1QL query using the default NotBounded scan consistency will not wait for any indexes to finish updating before running the query and returning results, meaning that results are returned quickly, but the query will not return any documents that are yet to be indexed.

    • With scan consistency set to RequestPlus, all outstanding document changes and index updates are processed before the query is run. Select this when consistency is always more important than performance.

    • For a middle ground, AtPlus is a "read your own write" (RYOW) option, which means it just waits for the documents that you specify to be indexed.

    For N1QL, the default consistency is not_bounded.

    Here’s how to specify the RequestPlus scan consistency level:

    val result = cluster.query(
      "select `travel-sample`.* from `travel-sample` limit 10;",
      QueryOptions().scanConsistency(QueryScanConsistency.RequestPlus())
    )

    And the AtPlus level is represented with QueryScanConsistency.ConsistentWith:

    val result = collection.upsert("id", content)
      .flatMap(upsertResult => {
        val ms = MutationState.from(upsertResult)
    
        cluster.query(
          "select `travel-sample`.* from `travel-sample` limit 10;",
            QueryOptions().scanConsistency(QueryScanConsistency.ConsistentWith(ms))
        )
      })
    
    result match {
      case Success(_) =>
      case Failure(err) => println(s"Operation failed with error $err")
    }