Querying Couchbase with N1QL – 用N1QL查询Couchbase

最后修改: 2017年 9月 25日

中文/混合/英文(键盘快捷键：t)

1. Overview

1.概述

In this article, we’ll be looking at querying a Couchbase Server with N1QL. In a simplified way, this is SQL for NoSQL databases – with the goal of making the transition from SQL/Relational databases to a NoSQL database system easier.

在这篇文章中，我们将研究如何使用N1QL查询Couchbase服务器。简而言之，这是适用于NoSQL数据库的SQL–目的是使从SQL/Relational数据库过渡到NoSQL数据库系统更加容易。

There are a couple of ways of interacting with the Couchbase Server; here, we’ll be using the Java SDK to interact with the database – as it is typical for Java applications.

有几种与Couchbase服务器交互的方法；在这里，我们将使用Java SDK与数据库进行交互–因为这是Java应用程序的典型做法。

2. Maven Dependencies

2.Maven的依赖性

We assume that a local Couchbase Server has been set up already; if that’s not the case, this guide can help you get started.

我们假设已经建立了本地的Couchbase服务器；如果不是这样的话，这个指南可以帮助你开始使用。

Let’s now add the dependency for Couchbase Java SDK to pom.xml:

现在让我们把Couchbase Java SDK的依赖性添加到pom.xml。

<dependency>
    <groupId>com.couchbase.client</groupId>
    <artifactId>java-client</artifactId>
    <version>2.5.0</version>
</dependency>

The latest version of Couchbase Java SDK can be found on Maven Central.

最新版本的Couchbase Java SDK可以在Maven Central上找到。

We’ll also be using Jackson library to map results returned from queries; let’s add its dependency to pom.xml as well:

我们还将使用Jackson库来映射查询返回的结果；让我们把它的依赖性也添加到pom.xml。

<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
    <version>2.13.0</version>
</dependency>

The latest version of Jackson library can be found on Maven Central.

Jackson库的最新版本可以在Maven Central上找到。

3. Connecting to a Couchbase Server

3.连接到Couchbase服务器

Now, that the project is set up with the right dependencies, let’s connect to Couchbase Server from a Java application.

现在，该项目已经建立了正确的依赖关系，让我们从一个Java应用程序连接到Couchbase服务器。

First, we need to start the Couchbase Server – if it’s not running already.

首先，我们需要启动Couchbase服务器–如果它还没有运行的话。

A guide to starting and stopping a Couchbase Server can be found here.

关于启动和停止Couchbase服务器的指南可以在这里找到。

Let’s connect to a Couchbase Bucket:

让我们连接到Couchbase的Bucket：。

Cluster cluster = CouchbaseCluster.create("localhost");
Bucket bucket = cluster.openBucket("test");

What we did was to connect to the Couchbase Cluster and then obtain the Bucket object.

我们所做的是连接到Couchbase Cluster，然后获得Bucket对象。

The name of the bucket in the Couchbase cluster is test and can be created using the Couchbase Web Console. When we’re done with all the database operations, we can close the particular bucket we’ve opened.

Couchbase集群中的桶的名字是test，可以使用Couchbase Web Console创建。当我们完成了所有的数据库操作后，我们可以关闭我们所打开的特定桶。

On the other hand, we can disconnect from the cluster – which will eventually close all the buckets:

另一方面，我们可以断开与集群的连接–这将最终关闭所有的桶。

bucket.close();
cluster.disconnect();

4. Inserting Documents

4.插入文件

Couchbase is a document-oriented database system. Let’s add a new document to the test bucket:

Couchbase是一个面向文档的数据库系统。让我们在testbucket中添加一个新的文档。

JsonObject personObj = JsonObject.create()
  .put("name", "John")
  .put("email", "john@doe.com")
  .put("interests", JsonArray.from("Java", "Nigerian Jollof"));

String id = UUID.randomUUID().toString();
JsonDocument doc = JsonDocument.create(id, personObj);
bucket.insert(doc);

First, we created a JSON personObj and provided some initial data. Keys can be seen as columns in a relational database system.

首先，我们创建了一个JSON personObj并提供了一些初始数据。键可以被看作是关系型数据库系统中的列。

From the person object, we created a JSON document using JsonDocument.create(), which we’ll insert into the bucket. Note that we generate a random id using java.util.UUID class.

从这个人的对象中，我们使用JsonDocument.create()创建了一个JSON文档，我们将把它插入桶中。请注意，我们使用java.util.UUID类生成一个随机的id。

The inserted document can be seen in the Couchbase Web Console at http://localhost:8091 or by calling the bucket.get() with its id:

插入的文档可以在Couchbase Web控制台http://localhost:8091看到，或者通过调用bucket.get()的id看到。

System.out.println(bucket.get(id));

5. Basic N1QL SELECT Query

5.基本N1QL SELECT 查询

N1QL is a superset of SQL, and its syntax, naturally, looks similar.

N1QL是SQL的超集，其语法自然也是类似的。

For instance, the N1QL for selecting all documents in the test bucket is:

例如，选择测试桶中所有文件的N1QL是。

SELECT * FROM test

Let’s execute this query in the application:

让我们在应用程序中执行这个查询。

bucket.bucketManager().createN1qlPrimaryIndex(true, false);

N1qlQueryResult result
  = bucket.query(N1qlQuery.simple("SELECT * FROM test"));

First, we create a primary index using the createN1qlPrimaryIndex(), it’ll be ignored if it has been created before; creating it is compulsory before any query can be executed.

首先，我们使用createN1qlPrimaryIndex()创建一个主索引，如果它之前已经被创建，将被忽略；在执行任何查询之前，创建它是强制性的。

Then we use the bucket.query() to execute the N1QL query.

然后我们使用bucket.query()来执行N1QL查询。

N1qlQueryResult is an Iterable<N1qlQueryRow> object, and thus we can print out every row using forEach():

N1qlQueryResult是一个Iterable<N1qlQueryRow>对象，因此我们可以使用forEach()打印出每行。

result.forEach(System.out::println);

From the returned result, we can get N1qlMetrics object by calling result.info(). From the metrics object, we can get insights about the returned result – for example, the result and the error count:

从返回的result中，我们可以通过调用result.info()获得N1qlMetricsobject。从度量对象中，我们可以得到关于返回结果的洞察力–例如，结果和错误计数。

System.out.println("result count: " + result.info().resultCount());
System.out.println("error count: " + result.info().errorCount());

On the returned result, we can use the result.parseSuccess() to check if the query is syntactically correct and parsed successfully. We can use the result.finalSuccess() to determine if the execution of the query was successful.

在返回的result上，我们可以使用result.parseSuccess()来检查查询在语法上是否正确，是否被成功解析。我们可以使用result.finalSuccess()来确定查询的执行是否成功。

6. N1QL Query Statements

6.N1QL查询语句

Let’s take a look at the different N1QL Query statements and different ways of executing them via the Java SDK.

让我们来看看不同的N1QL查询语句和通过Java SDK执行它们的不同方法。

6.1. SELECT Statement

6.1.SELECT 语句

The SELECT statement in NIQL is just like a standard SQL SELECT. It consists of three parts:

NIQL中的SELECT语句就像一个标准的SQLSELECT。它由三部分组成。

  • SELECT – defines the projection of the documents to be returned
  • FROM – describes the keyspace to fetch the documents from; keyspace is synonymous with table name in SQL database systems
  • WHERE – specifies the additional filtering criteria

The Couchbase Server comes with some sample buckets (databases). If they were not loaded during initial setup, the Settings section of the Web Console has a dedicated tab for setting them up.

Couchbase服务器自带了一些样本桶（数据库）。如果它们在初始设置时没有被加载，Web Console的Settings部分有一个专门的标签用于设置它们。

We’ll be using the travel-sample bucket. The travel-sample bucket contains data for airlines, landmark, airports, hotels, and routes. The data model can be found here.

我们将使用travel-sample桶。travel-sample桶包含航空公司、地标、机场、酒店和路线的数据。该数据模型可以在这里找到。

Let’s select 100 airline records from the travel-sample data:

让我们从旅行样本数据中选择100条航空公司的记录。

String query = "SELECT name FROM `travel-sample` " +
  "WHERE type = 'airport' LIMIT 100";
N1qlQueryResult result1 = bucket.query(N1qlQuery.simple(query));

The N1QL query, as can be seen above, looks very similar to SQL. Note that the keyspace name has to be put in backtick (`) because it contains a hyphen.

从上面可以看出，N1QL查询，看起来与SQL非常相似。请注意，关键空间的名称必须放在后缀（`）中，因为它包含一个连字符。

N1qlQueryResult is just a wrapper around the raw JSON data returned from the database. It extends Iterable<N1qlQueryRow> and can be looped over.

N1qlQueryResult只是对从数据库返回的原始JSON数据的一个封装。它扩展了Iterable<N1qlQueryRow>，可以循环使用。

Invoking result1.allRows() will return all the rows in a List<N1qlQueryRow> object. This is useful for processing results with the Stream API and/or accessing each result via index:

调用result1.allRows()将返回List<N1qlQueryRow>对象中的所有行。这对于用Stream API处理结果和/或通过索引访问每个结果是很有用的。

N1qlQueryRow row = result1.allRows().get(0);
JsonObject rowJson = row.value();
System.out.println("Name in First Row " + rowJson.get("name"));

We got the first row of the returned results, and we use row.value() to get a JsonObject – which maps the row to a key-value pair, and the key corresponds to the column name.

我们得到了返回结果的第一行，我们使用row.value()来得到一个JsonObject–它将该行映射为一个键值对，而键值对应于列名。

So we got the value of column, name, for the first row using the get(). It’s as easy as that.

所以我们用get()得到了第一行的name,列的值。就这么简单。

So far we have been using simple N1QL query. Let’s look at the parameterized statement in N1QL.

到目前为止，我们一直在使用简单的N1QL查询。让我们看看N1QL中的参数化语句。

In this query, we’re going to use the wildcard (*) symbol for selecting all the fields in the travel-sample records where type is an airport.

在这个查询中，我们将使用通配符（*）符号来选择travel-sample记录中type为airport的所有字段。

The type will be passed to the statement – as a parameter. Then we process the returned result:

类型将被传递给语句–作为一个参数。然后我们处理返回的结果。

JsonObject pVal = JsonObject.create().put("type", "airport");
String query = "SELECT * FROM `travel-sample` " +
  "WHERE type = $type LIMIT 100";
N1qlQueryResult r2 = bucket.query(N1qlQuery.parameterized(query, pVal));

We created a JsonObject to hold the parameters as a key-value pair. The value of the key ‘type’, in the pVal object, will be used to replace the $type placeholder in the query string.

我们创建了一个JsonObject来保存参数，作为一个键值对。在pVal对象中的键’type’的值，将被用来替换$type字符串中的占位符。

N1qlQuery.parameterized() accepts a query string that contains one or more placeholders and a JsonObject as demonstrated above.

N1qlQuery.参数化()接受一个包含一个或多个占位符的查询字符串和一个JsonObject，如上所示。

In the previous sample query above, we only select a column – name. This makes it easy to map the returned result into a JsonObject.

在上面的示例查询中，我们只选择了一列–名字。这使得我们很容易将返回的结果映射到一个JsonObject。

But now that we use the wildcard (*) in the select statement, it is not that simple. The returned result is a raw JSON string:

但现在我们在选择语句中使用通配符（*），就没有那么简单了。返回的结果是一个原始的JSON字符串。

[  
  {  
    "travel-sample":{  
      "airportname":"Calais Dunkerque",
      "city":"Calais",
      "country":"France",
      "faa":"CQF",
      "geo":{  
        "alt":12,
        "lat":50.962097,
        "lon":1.954764
      },
      "icao":"LFAC",
      "id":1254,
      "type":"airport",
      "tz":"Europe/Paris"
    }
  },

So what we need is a way to map each row to a structure that allows us to access the data by specifying the column name.

因此，我们需要的是一种方法，将每一行映射到一个结构，使我们能够通过指定列名来访问数据。

Therefore, let’s create a method that will accept N1qlQueryResult and then map every row in the result to a JsonNode object.

因此，让我们创建一个方法，接受N1qlQueryResult，然后将结果中的每一行映射为JsonNode对象。

We choose JsonNode because it can handle a broad range of JSON data structures and we can easily navigate it:

我们选择JsonNode是因为它可以处理广泛的JSON数据结构，而且我们可以轻松地浏览它。

public static List<JsonNode> extractJsonResult(N1qlQueryResult result) {
  return result.allRows().stream()
    .map(row -> {
        try {
            return objectMapper.readTree(row.value().toString());
        } catch (IOException e) {
            logger.log(Level.WARNING, e.getLocalizedMessage());
            return null;
        }
    })
    .filter(Objects::nonNull)
    .collect(Collectors.toList());
}

We processed each row in the result using the Stream API. We mapped each row to a JsonNode object and then return the result as a List of JsonNodes.

我们使用StreamAPI处理结果中的每一行。我们将每一行映射到一个JsonNode对象，然后将结果作为List的JsonNodes返回。

Now we can use the method to process the returned result from the last query:

现在我们可以使用该方法来处理上次查询返回的结果。

List<JsonNode> list = extractJsonResult(r2);
System.out.println(
  list.get(0).get("travel-sample").get("airportname").asText());

From the example JSON output shown previously, every row has a key the correlates to the keyspace name specified in the SELECT query – which is travel-sample in this case.

从前面显示的JSON输出示例来看，每一行都有一个与SELECT 查询中指定的关键空间名称相关的键–在这个例子中是travel-sample。

So we got the first row in the result, which is a JsonNode. Then we traverse the node to get to the airportname key, that is then printed as a text.

所以我们得到了结果中的第一行，它是一个JsonNode。然后我们遍历这个节点，找到airportname键，然后将其打印成文本。

The example raw JSON output shared earlier provides more clarity as per the structure of the returned result.

前面分享的原始JSON输出的例子提供了更清晰的返回结果的结构。

6.2. SELECT Statement Using N1QL DSL

6.2.使用N1QL DSL的SELECT语句

Other than using raw string literals for building queries we can also use N1QL DSL which comes with the Java SDK we are using.

除了使用原始的字符串字数来建立查询外，我们还可以使用N1QL DSL，它是我们所使用的Java SDK所附带的。

For example, the above string query can be formulated with the DSL thus:

例如，上面的字符串查询可以用DSL这样制定。

Statement statement = select("*")
  .from(i("travel-sample"))
  .where(x("type").eq(s("airport")))
  .limit(100);
N1qlQueryResult r3 = bucket.query(N1qlQuery.simple(statement));

The DSL is fluent and can be interpreted easily. The data selection classes and methods are in com.couchbase.client.java.query.Select class.

DSL是流畅的，可以很容易地被解释。数据选择类和方法都在com.couchbase.client.java.query.Select类中。

Expression methods like i(), eq(), x(), s() are in com.couchbase.client.java.query.dsl.Expression class. Read more about the DSL here.

像i(), eq(), x(), s()这样的表达式方法在com.couchbase.client.java.query.dsl.Expression类中。阅读更多关于DSL的信息这里。。

N1QL select statements can also have OFFSET, GROUP BY and ORDER BY clauses. The syntax is pretty much like that of standard SQL, and its reference can be found here.

N1QL选择语句也可以有OFFSET、GROUP BY和ORDER BY子句。其语法与标准SQL的语法基本相同，其参考资料可以在这里找到。

The WHERE clause of N1QL can take Logical Operators AND, OR, and NOT in its definitions. In addition to this, N1QL has provision for comparison operators like >, ==, !=, IS NULL and others.

N1QL的WHERE子句可以在其定义中采用逻辑运算符AND、OR和NOT。除此之外，N1QL还规定了比较运算符，如>、==、!=、IS NULL和其他。

There are also other operators that make accessing stored documents easy – the string operators can be used to concatenate fields to form a single string, and the nested operators can be used to slice arrays and cherry pick fields or element.

还有其他一些运算符，使访问存储的文件变得简单–字符串运算符可用于连接字段以形成一个字符串，而嵌套运算符可用于切分数组并挑选字段或元素。

Let’s see these in action.

让我们看看这些行动。

This query selects the city column, concatenate the airportname and faa columns as portname_faa from the travel-sample bucket where the country column ends with ‘States’‘, and the latitude of the airport is greater than or equal to 70:

这个查询选择了城市列，将机场名称和faa列串联为portname_fa，从travel-sample桶，其中国家列以‘states’‘结束，并且机场的纬度大于或等于70。

String query2 = "SELECT t.city, " +
  "t.airportname || \" (\" || t.faa || \")\" AS portname_faa " +
  "FROM `travel-sample` t " +
  "WHERE t.type=\"airport\"" +
  "AND t.country LIKE '%States'" +
  "AND t.geo.lat >= 70 " +
  "LIMIT 2";
N1qlQueryResult r4 = bucket.query(N1qlQuery.simple(query2));
List<JsonNode> list3 = extractJsonResult(r4);
System.out.println("First Doc : " + list3.get(0));

We can do the same thing using N1QL DSL:

我们可以使用N1QL DSL做同样的事情。

Statement st2 = select(
  x("t.city, t.airportname")
  .concat(s(" (")).concat(x("t.faa")).concat(s(")")).as("portname_faa"))
  .from(i("travel-sample").as("t"))
  .where( x("t.type").eq(s("airport"))
  .and(x("t.country").like(s("%States")))
  .and(x("t.geo.lat").gte(70)))
  .limit(2);
N1qlQueryResult r5 = bucket.query(N1qlQuery.simple(st2));
//...

Let’s look at other statements in N1QL. We’ll be building on the knowledge we’ve acquired in this section.

让我们来看看N1QL中的其他语句。我们将在本节所获得的知识基础上进行学习。

6.3. INSERT Statement

6.3.INSERTStatement

The syntax for the insert statement in N1QL is:

N1QL中插入语句的语法是：。

INSERT INTO `travel-sample` ( KEY, VALUE )
VALUES("unique_key", { "id": "01", "type": "airline"})
RETURNING META().id as docid, *;

Where travel-sample is the keyspace name, unique_key is the required non-duplicate key for the value object that follows it.

其中travel-sample是钥匙空间名称，unique_key是后面的值对象所需的不重复的钥匙。

The last segment is the RETURNING statement that specifies what gets returned.

最后一段是RETURNING语句，指定返回的内容。

In this case, the id of the inserted document is returned as docid. The wildcard (*) signifies that other attributes of the added document should be returned as well – separately from docid. See the sample result below.

在这种情况下，插入的文档的id被返回为docid。通配符(*)表示添加的文档的其他属性也应被返回–与docid分开。请看下面的示例结果。

Executing the following statement in the Query tab of Couchbase Web Console will insert a new record into the travel-sample bucket:

在Couchbase Web Console的Query选项卡中执行以下语句将在travel-sample桶中插入一条新记录。

INSERT INTO `travel-sample` (KEY, VALUE)
VALUES('cust1293', {"id":"1293","name":"Sample Airline", "type":"airline"})
RETURNING META().id as docid, *

Let’s do the same thing from a Java app. First, we can use a raw query like this:

让我们在一个Java应用程序中做同样的事情。首先，我们可以使用像这样的原始查询。

String query = "INSERT INTO `travel-sample` (KEY, VALUE) " +
  " VALUES(" +
  "\"cust1293\", " +
  "{\"id\":\"1293\",\"name\":\"Sample Airline\", \"type\":\"airline\"})" +
  " RETURNING META().id as docid, *";
N1qlQueryResult r1 = bucket.query(N1qlQuery.simple(query));
r1.forEach(System.out::println);

This will return the id of the inserted document as docid separately and the complete document body separately:

这将分别返回作为docid的被插入文档的id和完整的文档正文。

{  
  "docid":"cust1293",
  "travel-sample":{  
    "id":"1293",
    "name":"Sample Airline",
    "type":"airline"
  }
}

However, since we’re using the Java SDK, we can do it the object way by creating a JsonDocument that is then inserted into the bucket via the Bucket API:

然而，由于我们使用的是Java SDK，我们可以通过创建一个JsonDocument，然后通过BucketAPI插入桶中，以对象的方式进行。

JsonObject ob = JsonObject.create()
  .put("id", "1293")
  .put("name", "Sample Airline")
  .put("type", "airline");
bucket.insert(JsonDocument.create("cust1295", ob));

Instead of using the insert() we can use upsert() which will update the document if there is an existing document with the same unique identifier cust1295.

我们可以不使用insert()，而是使用upsert()，如果有一个具有相同唯一标识符的现有文件cust1295，它将更新该文件。

As it is now, using insert() will throw an exception if that same unique id already exists.

就像现在这样，如果相同的唯一ID已经存在，使用insert()将抛出一个异常。

The insert(), however, if successful, will return a JsonDocument that contains the unique id and entries of the inserted data.

然而，insert()，如果成功，将返回一个JsonDocument，其中包含插入数据的唯一ID和条目。

The syntax for bulk insert using N1QL is:

使用N1QL进行批量插入的语法是。

INSERT INTO `travel-sample` ( KEY, VALUE )
VALUES("unique_key", { "id": "01", "type": "airline"}),
VALUES("unique_key", { "id": "01", "type": "airline"}),
VALUES("unique_n", { "id": "01", "type": "airline"})
RETURNING META().id as docid, *;

We can perform bulk operations with the Java SDK using Reactive Java that underlines the SDK. Let’s add ten documents into a bucket using batch process:

我们可以使用强调SDK的Reactive Java对Java SDK进行批量操作。让我们使用批处理将十个文件添加到一个桶中。

List<JsonDocument> documents = IntStream.rangeClosed(0,10)
  .mapToObj( i -> {
      JsonObject content = JsonObject.create()
        .put("id", i)
        .put("type", "airline")
        .put("name", "Sample Airline "  + i);
      return JsonDocument.create("cust_" + i, content);
  }).collect(Collectors.toList());

List<JsonDocument> r5 = Observable
  .from(documents)
  .flatMap(doc -> bucket.async().insert(doc))
  .toList()
  .last()
  .toBlocking()
  .single();

r5.forEach(System.out::println);

First, we generate ten documents and put them into a List; then we used RxJava to perform the bulk operation.

首先，我们生成十个文件，并将它们放入一个List;然后我们使用RxJava来执行批量操作。

Finally, we print out the result of each insert – which has been accumulated to form a List.

最后，我们打印出每次插入的结果–这些结果已经累积成一个List。

The reference for performing bulk operations in the Java SDK can be found here. Also, the reference for insert statement can be found here.

在Java SDK中执行批量操作的参考资料可以在这里找到。此外，插入语句的参考资料可以在这里找到。

6.4. UPDATE Statement

6.4.更新声明

N1QL also has UPDATE statement. It can update documents identified by their unique keys. We can use the update statement to either SET (update) values of an attribute or UNSET (remove) an attribute altogether.

N1QL也有UPDATE语句。它可以更新由其唯一键识别的文件。我们可以使用更新语句来SET（更新）一个属性的值或UNSET（完全删除）一个属性。

Let’s update one of the documents we recently inserted into the travel-sample bucket:

让我们更新我们最近插入到travel-sample桶中的一个文件。

String query2 = "UPDATE `travel-sample` USE KEYS \"cust_1\" " +
  "SET name=\"Sample Airline Updated\" RETURNING name";
N1qlQueryResult result = bucket.query(N1qlQuery.simple(query2));
result.forEach(System.out::println);

In the above query, we updated the name attribute of a cust_1 entry in the bucket to Sample Airline Updated, and we instruct the query to return the updated name.

在上面的查询中，我们将桶中的cust_1条目的name属性更新为Sample Airline Updated,，并且我们指示查询返回更新的名称。

As stated earlier, we can also achieve the same thing by constructing a JsonDocument with the same id and use the upsert() of Bucket API to update the document:

如前所述，我们也可以通过构建一个具有相同id的JsonDocument并使用Bucket API的upsert() 来更新该文档来实现同样的目的。

JsonObject o2 = JsonObject.create()
  .put("name", "Sample Airline Updated");
bucket.upsert(JsonDocument.create("cust_1", o2));

In this next query, let’s use the UNSET command to remove the name attribute and return the affected document:

在接下来的查询中，让我们使用UNSET命令来删除name属性并返回受影响的文档。

String query3 = "UPDATE `travel-sample` USE KEYS \"cust_2\" " +
  "UNSET name RETURNING *";
N1qlQueryResult result1 = bucket.query(N1qlQuery.simple(query3));
result1.forEach(System.out::println);

The returned JSON string is:

返回的JSON字符串是。

{  
  "travel-sample":{  
    "id":2,
    "type":"airline"
  }
}

Take note of the missing name attribute – it has been removed from the document object. N1QL update syntax reference can be found here.

请注意缺少的name属性–它已经从文档对象中删除。N1QL更新语法参考可以在这里找到。

So we have a look at inserting new documents and updating documents. Now let’s look at the final piece of the CRUD acronym – DELETE.

所以我们看了一下插入新文件和更新文件的情况。现在让我们来看看CRUD缩写的最后一块–DELETE。

6.5. DELETE Statement

6.5.DELETE 声明

Let’s use the DELETE query to delete some of the documents we have created earlier. We’ll use the unique id to identify the document with the USE KEYS keyword:

让我们使用DELETE查询来删除我们之前创建的一些文档。我们将使用唯一的id来识别带有USE KEYS关键字的文档。

String query4 = "DELETE FROM `travel-sample` USE KEYS \"cust_50\"";
N1qlQueryResult result4 = bucket.query(N1qlQuery.simple(query4));

N1QL DELETE statement also takes a WHERE clause. So we can use conditions to select the records to be deleted:

N1QL DELETE语句也需要一个WHERE子句。因此，我们可以使用条件来选择要删除的记录。

String query5 = "DELETE FROM `travel-sample` WHERE id = 0 RETURNING *";
N1qlQueryResult result5 = bucket.query(N1qlQuery.simple(query5));

We can also use the remove() from the bucket API directly:

我们也可以直接使用bucket API的remove()。

bucket.remove("cust_2");

Much simpler right? Yes, but now we also know how to do it using N1QL. The reference doc for DELETE syntax can be found here.

简单多了吧？是的，但现在我们也知道如何使用N1QL来做。DELETE语法的参考文档可以在这里找到。

7. N1QL Functions and Sub-Queries

7.N1QL函数和子查询

N1QL did not just resemble SQL regarding syntax alone; it goes all the way to some functionalities. In SQL, we’ve some functions like COUNT() that can be used within the query string.

N1QL不仅仅是在语法方面类似于SQL，它还包括了一些功能。在SQL中，我们有一些函数，如COUNT() ，可以在查询字符串中使用。

N1QL, in the same fashion, has its functions that can be used in the query string.

N1QL，以同样的方式，有其可以在查询字符串中使用的函数。

For example, this query will return the total number of landmark records that are in the travel-sample bucket:

例如，这个查询将返回travel-sample桶中的地标记录总数。

SELECT COUNT(*) as landmark_count FROM `travel-sample` WHERE type = 'landmark'

In previous examples above, we’ve used the META function in UPDATE statement to return the id of updated document.

在上面的例子中，我们在UPDATE语句中使用了META函数来返回更新文档的id。

There are string method that can trim trailing white spaces, make lower and upper case letters and even check if a string contains a token. Let’s use some of these functions in a query:

有一些字符串方法可以修剪尾部的空白，使小写和大写字母，甚至检查一个字符串是否包含一个标记。让我们在查询中使用这些函数中的一些。

Let’s use some of these functions in a query:

让我们在一个查询中使用其中的一些函数。

INSERT INTO `travel-sample` (KEY, VALUE) 
VALUES(LOWER(UUID()), 
  {"id":LOWER(UUID()), "name":"Sample Airport Rand", "created_at": NOW_MILLIS()})
RETURNING META().id as docid, *

The query above inserts a new entry into the travel-sample bucket. It uses the UUID() function to generate a unique random id which was converted to lower case using the LOWER() function.

上面的查询在travel-samplebucket中插入了一个新条目。它使用UUID()函数来生成一个唯一的随机ID，并使用LOWER()函数转换为小写。

The NOW_MILLIS() method was used to set the current time, in milliseconds, as the value of the created_at attribute. The complete reference of N1QL functions can be found here.

NOW_MILLIS()方法被用来设置当前的时间，单位是毫秒，作为created_at属性的值。N1QL函数的完整参考可以在这里找到。

Sub-queries come in handy at times, and N1QL has provision for them. Still using the travel-sample bucket, let’s select the destination airport of all routes for a particular airline – and get the country they are located in:

子查询有时会很方便，N1QL对它们有规定。仍然使用travel-sample桶，让我们选择某家航空公司所有航线的目的地机场，并获得它们所在的国家。

SELECT DISTINCT country FROM `travel-sample` WHERE type = "airport" AND faa WITHIN 
  (SELECT destinationairport 
  FROM `travel-sample` t WHERE t.type = "route" and t.airlineid = "airline_10")

The sub-query in the above query is enclosed within parentheses and returns the destinationairport attribute, of all routes associated with airline_10, as a collection.

上述查询中的子查询被括在括号内，并作为一个集合返回与airline_10相关的所有航线的destinationairport属性。

The destinationairport attributes correlate to the faa attribute on airport documents in the travel-sample bucket. The WITHIN keyword is part of collection operators in N1QL.

destinationairport属性与travel-sample桶中airport文件的faa属性相关。WITHIN关键字是N1QL中集合运算符的一部分。

Now, that we’ve got the country of destination airport of all routes for airline_10. Let’s do something interesting by looking for hotels within that country:

现在，我们已经得到了airline_10所有航线的目的地机场的国家。让我们做一些有趣的事情，寻找这个国家的酒店。

SELECT name, price, address, country FROM `travel-sample` h 
WHERE h.type = "hotel" AND h.country WITHIN
  (SELECT DISTINCT country FROM `travel-sample` 
  WHERE type = "airport" AND faa WITHIN 
  (SELECT destinationairport FROM `travel-sample` t 
  WHERE t.type = "route" and t.airlineid = "airline_10" )
  ) LIMIT 100

The previous query was used as a sub-query in the WHERE constraint of the outermost query. Take note of the DISTINCT keyword – it does the same thing as in SQL – returns non-duplicate data.

前面的查询被用作最外层查询的WHERE约束中的一个子查询。请注意DISTINCT关键字–它的作用与SQL相同–返回非重复的数据。

All the query examples here can be executed using the SDK as demonstrated earlier in this article.

这里所有的查询例子都可以使用SDK来执行，正如本文前面所演示的那样。

8. Conclusion

8.结论

N1QL takes the process of querying the document-based database like Couchbase to another whole level. It doesn’t only simplify this process, it also makes switching from a relational database system a lot easier as well.

N1QL将Couchbase这样的基于文档的数据库的查询过程提升到了另一个整体水平。它不仅简化了这一过程，还使从关系型数据库系统的转换也变得容易得多。

We’ve looked at the N1QL query in this article; the main documentation can be found here. And you can learn about Spring Data Couchbase here.

我们已经在这篇文章中了解了N1QL查询；主要的文档可以在这里找到。而且你可以了解Spring Data Couchbase 这里。

As always, the complete source code is available over on Github.

一如既往，完整的源代码可在Github上获取。