Arangodb 简明教程

Querying the Data with AQL

在本章中，我们将讨论如何用 AQL 查询数据。我们的前几章已经讨论过 ArangoDB 已经开发了自己的查询语言，并且命名为 AQL。

In this chapter, we will discuss how to query the data with AQL. We have already discussed in our previous chapters that ArangoDB has developed its own query language and that it goes by the name AQL.

现在让我们开始与 AQL 交互。如以下图片所示，在 Web 界面中，按下导航栏顶部的“@ {s0} ”选项卡。会出现一个空白查询编辑器。

Let us now start interacting with AQL. As shown in the image below, in the web interface, press the AQL Editor tab placed at the top of the navigation bar. A blank query editor will appear.

需要时，可以通过单击右上角的“查询”或“结果”选项卡在结果视图和编辑器之间进行切换，如下面的图像所示 −

When need, you can switch to the editor from the result view and vice-versa, by clicking the Query or the Result tabs in the top right corner as shown in the image below −

在其他功能中，编辑器具有语法高亮显示、撤消/重做功能和查询保存。有关详细参考，可以参阅官方文档。我们将重点介绍 AQL 查询编辑器的一些基本且常用的功能。

Among other things, the editor has syntax highlighting, undo/redo functionality, and query saving. For a detailed reference, one can see the official documentation. We will highlight few basic and commonly-used features of the AQL query editor.

AQL Fundamentals

在 AQL 中，查询表示要实现的最终结果，但不是实现最终结果的过程。此功能通常称为语言的声明属性。此外，AQL 可以查询并修改数据，因此可以通过组合这两个过程创建复杂的查询。

In AQL, a query represents the end result to be achieved, but not the process through which the end result is to be achieved. This feature is commonly known as a declarative property of the language. Moreover, AQL can query as well modify the data, and thus complex queries can be created by combining both the processes.

请注意，AQL 完全符合 ACID。读取或修改查询要么全部结束，要么根本不结束。即使读取文档数据也将以一致的数据单元完成。

Please note that AQL is entirely ACID-compliant. Reading or modifying queries will either conclude in whole or not at all. Even reading a document’s data will finish with a consistent unit of the data.

我们将向我们已经创建的 songs 集合中添加两个新的 @ {s1} 。您可以复制以下查询并将其粘贴到 AQL 编辑器中，而不是输入 −

We add two new songs to the songs collection we have already created. Instead of typing, you can copy the following query, and paste it in the AQL editor −

FOR song IN [
   {
      title: "Air-Minded Executive", lyricist: "Johnny Mercer",
      composer: "Bernie Hanighen", Year: 1940, _key: "Air-Minded"
   },

   {
      title: "All Mucked Up", lyricist: "Johnny Mercer", composer:
      "Andre Previn", Year: 1974, _key: "All_Mucked"
   }
]
INSERT song IN songs

按左下方的“执行”按钮。

Press the Execute button at the lower left.

它将在 @ {s2} 集合中写入两个新文档。

It will write two new documents in the songs collection.

此查询描述了 FOR 循环如何在 AQL 中工作；它遍历 JSON 编码文档列表，对集合中每个文档执行编码操作。不同的操作可以是创建新结构、过滤、选择文档、修改或向数据库中插入文档（请参阅直接示例）。从本质上讲，AQL 可以有效地执行 CRUD 操作。

This query describes how the FOR loop works in AQL; it iterates over the list of JSON encoded documents, performing the coded operations on each one of the documents in the collection. The different operations can be creating new structures, filtering, selecting documents, modifying, or inserting documents into the database (refer the instantaneous example). In essence, AQL can perform the CRUD operations efficiently.

要查找我们数据库中的所有歌曲，让我们再次运行以下查询，这相当于 SQL 类型数据库的 @ {s3} （因为编辑器会记住上次查询，按 @ {s4} 清除编辑器）−

To find all the songs in our database, let us once again run the following query, equivalent to a SELECT * FROM songs of an SQL-type database (because the editor memorizes the last query, press the New button to clean the editor) −

FOR song IN songs
RETURN song

结果集将显示迄今为止保存在 @ {s5} 集合中的歌曲列表，如下面的屏幕截图所示。

The result set will show the list of songs so far saved in the songs collection as shown in the screenshot below.

可以将 @ {s6} 和 @ {s7} 等操作添加到 @ {s8} 正文以缩小和排序结果。

Operations like FILTER, SORT and LIMIT can be added to the For loop body to narrow and order the result.

FOR song IN songs
FILTER song.Year > 1940
RETURN song

以上查询将在“结果”选项卡中显示 1940 年后创作的歌曲（见下图）。

The above query will give songs created after the year 1940 in the Result tab (see the image below).

此示例中使用了文档键，但任何其他属性也可以用作过滤的等价物。由于保证文档键是唯一的，因此没有超过一个文档将匹配此过滤器。对于其他属性，可能并非如此。要返回一组活动用户（由称为 status 的属性确定），按名称升序排列，我们使用以下语法 −

The document key is used in this example, but any other attribute can also be used as an equivalent for filtering. Since the document key is guaranteed to be unique, no more than a single document will match this filter. For other attributes this may not be the case. To return a subset of active users (determined by an attribute called status), sorted by name in ascending order, we use the following syntax −

FOR song IN songs
FILTER song.Year > 1940
SORT song.composer
RETURN song
LIMIT 2

我们特意包含此示例。在这里，我们观察到 AQL 用红色突出显示的查询语法错误消息。此语法突出显示错误并有助于调试您的查询，如下面的屏幕截图所示。

We have deliberately included this example. Here, we observe a query syntax error message highlighted in red by AQL. This syntax highlights the errors and is helpful in debugging your queries as shown in the screenshot below.

现在让我们运行正确的查询（注意更正）−

Let us now run the correct query (note the correction) −

FOR song IN songs
FILTER song.Year > 1940
SORT song.composer
LIMIT 2
RETURN song

Complex Query in AQL

AQL 为所有支持的数据类型配备了多个函数。查询中的变量分配允许构建非常复杂的嵌套结构。这样，数据密集型操作会移至后端的データ比移至客户端（例如浏览器）更近的位置。为了理解这一点，让我们首先将任意持续时间（长度）添加到歌曲中。

AQL is equipped with multiple functions for all supported data types. Variable assignment within a query allows to build very complex nested constructs. This way data-intensive operations move closer to the data at the backend than on to the client (such as browser). To understand this, let us first add the arbitrary durations (length) to songs.

首先，让我们从第一个函数开始，即 Update 函数 -

Let us start with the first function, i.e., the Update function −

UPDATE { _key: "All_Mucked" }
WITH { length: 180 }
IN songs

我们可以看到，文档已按上述屏幕截图中所示进行编写。

We can see one document has been written as shown in the above screenshot.

现在让我们更新其他文档（歌曲）。

Let us now update other documents (songs) too.

UPDATE { _key: "Affable_Balding" }
WITH { length: 200 }
IN songs

现在我们可以检查所有歌曲是否都有新属性 length -

We can now check that all our songs have a new attribute length −

FOR song IN songs
RETURN song

Output

[
   {
      "_key": "Air-Minded",
      "_id": "songs/Air-Minded",
      "_rev": "_VkC5lbS---",
      "title": "Air-Minded Executive",
      "lyricist": "Johnny Mercer",
      "composer": "Bernie Hanighen",
      "Year": 1940,
      "length": 210
   },

   {
      "_key": "Affable_Balding",
      "_id": "songs/Affable_Balding",
      "_rev": "_VkC4eM2---",
      "title": "Affable Balding Me",
      "lyricist": "Johnny Mercer",
      "composer": "Robert Emmett Dolan",
      "Year": 1950,
      "length": 200
   },

   {
      "_key": "All_Mucked",
      "_id": "songs/All_Mucked",
      "_rev": "_Vjah9Pu---",
      "title": "All Mucked Up",
      "lyricist": "Johnny Mercer",
      "composer": "Andre Previn",
      "Year": 1974,
      "length": 180
   },

   {
      "_key": "Accentchuate_The",
      "_id": "songs/Accentchuate_The",
      "_rev": "_VkC3WzW---",
      "title": "Accentchuate The Politics",
      "lyricist": "Johnny Mercer",
      "composer": "Harold Arlen",
      "Year": 1944,
      "length": 190
   }
]

为了说明 AQL 中的其他关键字（如 LET、FILTER、SORT 等）的用法，我们现在将歌曲的时长格式化为 mm:ss 格式。

To illustrate the use of other keywords of AQL such as LET, FILTER, SORT, etc., we now format the song’s durations in the mm:ss format.

Query

FOR song IN songs
FILTER song.length > 150
LET seconds = song.length % 60
LET minutes = FLOOR(song.length / 60)
SORT song.composer
RETURN
{
   Title: song.title,
   Composer: song.composer,
   Duration: CONCAT_SEPARATOR(':',minutes, seconds)
}

这一次，我们将返回歌曲标题以及时长。 Return 函数可让你为每个输入文档创建一个新 JSON 对象以返回。

This time we will return the song title together with the duration. The Return function lets you create a new JSON object to return for each input document.

我们现在来讨论 AQL 数据库的“连接”功能。

We will now talk about the ‘Joins’ feature of AQL database.

让我们首先创建一个集合 composer_dob 。接下来，我们将通过在查询框中运行以下查询来创建四份包含作曲家的假设出生日期的文档 -

Let us begin by creating a collection composer_dob. Further, we will create the four documents with the hypothetical date of births of the composers by running the following query in the query box −

FOR dob IN [
   {composer: "Bernie Hanighen", Year: 1909}
   ,
   {composer: "Robert Emmett Dolan", Year: 1922}
   ,
   {composer: "Andre Previn", Year: 1943}
   ,
   {composer: "Harold Arlen", Year: 1910}
]
INSERT dob in composer_dob

为了突出它与 SQL 的相似性，我们在 AQL 中提供了一个嵌套的 FOR-loop 查询，导致了 REPLACE 操作，它首先在内部循环中迭代，遍历所有作曲家的 dob，然后遍历所有关联的歌曲，创建包含属性 song_with_composer_key 的新文档，而不是属性 song 。

To highlight the similarity with SQL, we present a nested FOR-loop query in AQL, leading to the REPLACE operation, iterating first in the inner loop, over all the composers’ dob and then on all the associated songs, creating a new document containing attribute song_with_composer_key instead of the song attribute.

查询如下 -

Here goes the query −

FOR s IN songs
FOR c IN composer_dob
FILTER s.composer == c.composer

LET song_with_composer_key = MERGE(
   UNSET(s, 'composer'),
   {composer_key:c._key}
)
REPLACE s with song_with_composer_key IN songs

现在让我们再次运行查询 FOR song IN songs RETURN song ，看看歌曲集合如何改变。

Let us now run the query FOR song IN songs RETURN song again to see how the song collection has changed.

Output

[
   {
      "_key": "Air-Minded",
      "_id": "songs/Air-Minded",
      "_rev": "_Vk8kFoK---",
      "Year": 1940,
      "composer_key": "5501",
      "length": 210,
      "lyricist": "Johnny Mercer",
      "title": "Air-Minded Executive"
   },

   {
      "_key": "Affable_Balding",
      "_id": "songs/Affable_Balding",
      "_rev": "_Vk8kFoK--_",
      "Year": 1950,
      "composer_key": "5505",
      "length": 200,
      "lyricist": "Johnny Mercer",
      "title": "Affable Balding Me"
   },

   {
      "_key": "All_Mucked",
      "_id": "songs/All_Mucked",
      "_rev": "_Vk8kFoK--A",
      "Year": 1974,
      "composer_key": "5507",
      "length": 180,
      "lyricist": "Johnny Mercer",
      "title": "All Mucked Up"
   },

   {
      "_key": "Accentchuate_The",
      "_id": "songs/Accentchuate_The",
      "_rev": "_Vk8kFoK--B",
      "Year": 1944,
      "composer_key": "5509",
      "length": 190,
      "lyricist": "Johnny Mercer",
      "title": "Accentchuate The Politics"
   }
]

以上查询完成了数据迁移过程，向每首歌曲添加 composer_key 。

The above query completes the data migration process, adding the composer_key to each song.

现在，下一个查询仍然是一个嵌套的 FOR-loop 查询，但这一次却导致了连接操作，将关联作曲家的姓名（通过 composer_key 协助选取）添加到每首歌曲中 -

Now the next query is again a nested FOR-loop query, but this time leading to the Join operation, adding the associated composer’s name (picking with the help of composer_key) to each song −

FOR s IN songs
FOR c IN composer_dob
FILTER c._key == s.composer_key
RETURN MERGE(s,
{ composer: c.composer }
)

Output

[
   {
      "Year": 1940,
      "_id": "songs/Air-Minded",
      "_key": "Air-Minded",
      "_rev": "_Vk8kFoK---",
      "composer_key": "5501",
      "length": 210,
      "lyricist": "Johnny Mercer",
      "title": "Air-Minded Executive",
      "composer": "Bernie Hanighen"
   },

   {
      "Year": 1950,
      "_id": "songs/Affable_Balding",
      "_key": "Affable_Balding",
      "_rev": "_Vk8kFoK--_",
      "composer_key": "5505",
      "length": 200,
      "lyricist": "Johnny Mercer",
      "title": "Affable Balding Me",
      "composer": "Robert Emmett Dolan"
   },

   {
      "Year": 1974,
      "_id": "songs/All_Mucked",
      "_key": "All_Mucked",
      "_rev": "_Vk8kFoK--A",
      "composer_key": "5507",
      "length": 180,
      "lyricist": "Johnny Mercer",
      "title": "All Mucked Up",
      "composer": "Andre Previn"
   },

   {
      "Year": 1944,
      "_id": "songs/Accentchuate_The",
      "_key": "Accentchuate_The",
      "_rev": "_Vk8kFoK--B",
      "composer_key": "5509",
      "length": 190,
      "lyricist": "Johnny Mercer",
      "title": "Accentchuate The Politics",
      "composer": "Harold Arlen"
   }
]