用AQL查询数据

在本章中，我们将讨论如何使用AQL查询数据。我们已经在前面的章节中讨论过，ArangoDB已经开发了自己的查询语言，它的名字叫AQL。

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

如果需要，您可以通过单击右上角的查询或结果选项卡从结果视图切换到编辑器，反之亦然，如下图所示−

除此之外，编辑器还具有语法高亮显示、撤消/重做功能和查询保存功能。有关详细的参考资料，请参阅官方文档。我们将重点介绍AQL查询编辑器的一些基本和常用功能。

AQL基本

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

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

We add two new 歌曲 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

按左下角的执行按钮。

It will write two new documents in the 歌曲 collection.

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

To find all the songs in our database, let us once again run the following query, equivalent to a 从歌曲中选择* of an SQL-type database (because the editor memorizes the last query, press the *新建* button to clean the editor) −

FOR song IN songs
RETURN song

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

Operations like 筛选，排序 and 限制 can be added to the For循环 body to narrow and order the result.

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

上面的查询将在结果选项卡中给出1940年之后创建的歌曲（请参见下图）。

本例中使用了document键，但是任何其他属性也可以用作过滤的等价项。由于文档密钥保证是唯一的，因此最多只有一个文档与此筛选器匹配。对于其他属性，情况可能并非如此。要返回活动用户的子集（由名为status的属性确定），按名称升序排序，我们使用以下语法−

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

我们故意把这个例子包括在内。在这里，我们观察到一条查询语法错误消息，该消息由AQL以红色突出显示。此语法突出显示错误，并有助于调试查询，如下面的屏幕截图所示。

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

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

AQL中的复杂查询

AQL为所有支持的数据类型配备了多种功能。查询中的变量赋值允许构建非常复杂的嵌套结构。这样，数据密集型操作更接近后端的数据，而不是客户端（如浏览器）。为了理解这一点，让我们首先为歌曲添加任意的持续时间（长度）。

让我们从第一个函数开始，即Update函数−

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

我们可以看到一个文件已经被写在上面的截图所示。

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

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

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

FOR song IN songs
RETURN song

输出

[
   {
      "_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
   }
]

To illustrate the use of other keywords of AQL such as LET, FILTER, SORT, etc., we now format the song’s durations in the 毫米：不锈钢 format.

查询

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) 
}

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

我们现在将讨论AQL数据库的“连接”特性。

Let us begin by creating a collection 作曲家. 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

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 带作曲键的歌曲 instead of the 歌曲 attribute.

问题来了−

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

Let us now run the query 以歌换歌 again to see how the song collection has changed.

输出

[
   {
      "_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"
   }
]

The above query completes the data migration process, adding the 组合键 to each song.

现在，下一个查询再次是嵌套FOR循环查询，但这一次将导致Join操作，将关联的作曲家名称（在“composer\u key”的帮助下进行挑选）添加到每首歌曲中−

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

输出

[
   {
      "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"
   }
]