基础查询 #

一、查询概述 #

AQL（ArangoDB Query Language）是ArangoDB的查询语言，语法类似SQL但更适合文档和图数据。

1.1 基本结构 #

aql

FOR doc IN collection
    FILTER condition
    SORT field
    LIMIT count
    RETURN projection

1.2 查询执行顺序 #

text

1. FOR - 遍历集合
2. FILTER - 过滤数据
3. SORT - 排序
4. LIMIT - 限制数量
5. RETURN - 返回结果

二、FOR遍历 #

2.1 遍历集合 #

aql

FOR user IN users
    RETURN user

2.2 遍历数组 #

aql

FOR i IN [1, 2, 3, 4, 5]
    RETURN i * 2

输出：

json

[2, 4, 6, 8, 10]

2.3 遍历范围 #

aql

FOR i IN 1..5
    RETURN i

输出：

json

[1, 2, 3, 4, 5]

不包含结束值：

aql

FOR i IN 1..5
    RETURN i

2.4 嵌套FOR #

aql

FOR user IN users
    FOR order IN orders
        FILTER order.userId == user._key
        RETURN {
            user: user.name,
            orderId: order._key
        }

2.5 遍历对象属性 #

aql

FOR user IN users
    FOR key, value IN user.address
        RETURN {
            user: user.name,
            key: key,
            value: value
        }

三、FILTER过滤 #

3.1 基本过滤 #

aql

FOR user IN users
    FILTER user.age > 25
    RETURN user

3.2 多条件AND #

aql

FOR user IN users
    FILTER user.age > 25 AND user.city == "北京"
    RETURN user

3.3 多条件OR #

aql

FOR user IN users
    FILTER user.city == "北京" OR user.city == "上海"
    RETURN user

3.4 IN操作符 #

aql

FOR user IN users
    FILTER user.city IN ["北京", "上海", "广州"]
    RETURN user

3.5 NOT IN操作符 #

aql

FOR user IN users
    FILTER user.city NOT IN ["北京", "上海"]
    RETURN user

3.6 范围过滤 #

aql

FOR user IN users
    FILTER user.age >= 20 AND user.age <= 30
    RETURN user

简写形式：

aql

FOR user IN users
    FILTER 20 <= user.age <= 30
    RETURN user

3.7 正则表达式 #

aql

FOR user IN users
    FILTER user.email =~ "^[a-z]+@[a-z]+\\.[a-z]+$"
    RETURN user

忽略大小写：

aql

FOR user IN users
    FILTER user.name =~ "(?i)zhang"
    RETURN user

3.8 LIKE模糊匹配 #

aql

FOR user IN users
    FILTER user.name LIKE "%张%"
    RETURN user

3.9 数组过滤 #

aql

FOR user IN users
    FILTER "阅读" IN user.hobbies
    RETURN user

数组长度过滤：

aql

FOR user IN users
    FILTER LENGTH(user.hobbies) > 2
    RETURN user

3.10 NULL检查 #

aql

FOR user IN users
    FILTER user.deleted_at == null
    RETURN user

四、SORT排序 #

4.1 升序排序 #

aql

FOR user IN users
    SORT user.age ASC
    RETURN user

4.2 降序排序 #

aql

FOR user IN users
    SORT user.age DESC
    RETURN user

4.3 多字段排序 #

aql

FOR user IN users
    SORT user.city ASC, user.age DESC
    RETURN user

4.4 按表达式排序 #

aql

FOR user IN users
    SORT LENGTH(user.name) DESC
    RETURN user

4.5 随机排序 #

aql

FOR user IN users
    SORT RAND()
    RETURN user

五、LIMIT限制 #

5.1 限制数量 #

aql

FOR user IN users
    LIMIT 10
    RETURN user

5.2 分页查询 #

aql

FOR user IN users
    LIMIT 10, 20
    RETURN user

等价于：

aql

FOR user IN users
    LIMIT @offset, @count
    RETURN user

5.3 分页示例 #

aql

LET pageSize = 10
LET page = 2
FOR user IN users
    SORT user.name
    LIMIT (page - 1) * pageSize, pageSize
    RETURN user

5.4 LIMIT与SORT #

aql

FOR user IN users
    SORT user.score DESC
    LIMIT 10
    RETURN user

六、RETURN返回 #

6.1 返回整个文档 #

aql

FOR user IN users
    RETURN user

6.2 返回特定字段 #

aql

FOR user IN users
    RETURN user.name

6.3 返回对象 #

aql

FOR user IN users
    RETURN {
        name: user.name,
        email: user.email
    }

6.4 重命名字段 #

aql

FOR user IN users
    RETURN {
        userName: user.name,
        userEmail: user.email
    }

6.5 返回嵌套对象 #

aql

FOR user IN users
    RETURN {
        name: user.name,
        address: {
            city: user.address.city,
            street: user.address.street
        }
    }

6.6 DISTINCT去重 #

aql

FOR user IN users
    RETURN DISTINCT user.city

6.7 条件返回 #

aql

FOR user IN users
    RETURN {
        name: user.name,
        status: user.age >= 18 ? "成年" : "未成年"
    }

6.8 返回数组 #

aql

FOR user IN users
    RETURN [user.name, user.email]

七、LET变量 #

7.1 定义变量 #

aql

LET name = "ArangoDB"
RETURN name

7.2 定义数组变量 #

aql

LET cities = ["北京", "上海", "广州"]
FOR city IN cities
    RETURN city

7.3 定义查询结果变量 #

aql

LET activeUsers = (
    FOR user IN users
        FILTER user.status == "active"
        RETURN user
)
FOR user IN activeUsers
    RETURN user.name

7.4 复杂查询示例 #

aql

LET threshold = 25
LET cities = ["北京", "上海"]
FOR user IN users
    FILTER user.age > threshold AND user.city IN cities
    RETURN {
        name: user.name,
        age: user.age,
        city: user.city
    }

八、聚合查询 #

8.1 统计数量 #

aql

FOR user IN users
    COLLECT WITH COUNT INTO count
    RETURN count

8.2 分组统计 #

aql

FOR user IN users
    COLLECT city = user.city WITH COUNT INTO count
    RETURN {
        city: city,
        count: count
    }

8.3 分组聚合 #

aql

FOR user IN users
    COLLECT city = user.city AGGREGATE
        avgAge = AVG(user.age),
        maxAge = MAX(user.age),
        minAge = MIN(user.age)
    RETURN {
        city: city,
        avgAge: avgAge,
        maxAge: maxAge,
        minAge: minAge
    }

8.4 聚合函数 #

函数	说明
COUNT()	计数
SUM()	求和
AVG()	平均值
MIN()	最小值
MAX()	最大值
STDDEV()	标准差
VARIANCE()	方差

九、子查询 #

9.1 基本子查询 #

aql

FOR user IN users
    LET orderCount = (
        FOR order IN orders
            FILTER order.userId == user._key
            COLLECT WITH COUNT INTO count
            RETURN count
    )[0]
    RETURN {
        name: user.name,
        orderCount: orderCount
    }

9.2 子查询返回数组 #

aql

FOR user IN users
    LET userOrders = (
        FOR order IN orders
            FILTER order.userId == user._key
            RETURN order._key
    )
    RETURN {
        name: user.name,
        orders: userOrders
    }

十、查询选项 #

10.1 查询缓存 #

aql

FOR user IN users
    RETURN user
OPTIONS { cache: true }

10.2 全量计数 #

aql

FOR user IN users
    LIMIT 10
    RETURN user
OPTIONS { fullCount: true }

10.3 查询分析 #

aql

FOR user IN users
    FILTER user.age > 25
    RETURN user
OPTIONS { profile: true }

十一、实战示例 #

11.1 用户列表查询 #

aql

FOR user IN users
    FILTER user.status == "active"
    SORT user.createdAt DESC
    LIMIT 0, 20
    RETURN {
        id: user._key,
        name: user.name,
        email: user.email,
        avatar: user.avatar,
        createdAt: user.createdAt
    }

11.2 搜索用户 #

aql

LET keyword = LOWER(@keyword)
FOR user IN users
    FILTER CONTAINS(LOWER(user.name), keyword)
    OR CONTAINS(LOWER(user.email), keyword)
    SORT user.name ASC
    LIMIT 50
    RETURN user

11.3 按标签筛选 #

aql

FOR user IN users
    FILTER @tag IN user.tags
    SORT user.score DESC
    LIMIT 20
    RETURN user

11.4 复杂条件查询 #

aql

FOR user IN users
    FILTER user.status == "active"
    AND user.age >= 18
    AND user.age <= 60
    AND user.city IN ["北京", "上海", "广州"]
    AND LENGTH(user.skills) > 0
    SORT user.score DESC, user.createdAt DESC
    LIMIT 100
    RETURN {
        id: user._key,
        name: user.name,
        score: user.score
    }

11.5 统计报表 #

aql

FOR user IN users
    COLLECT 
        city = user.city,
        status = user.status
    AGGREGATE
        count = COUNT(),
        avgAge = AVG(user.age),
        totalScore = SUM(user.score)
    SORT count DESC
    RETURN {
        city: city,
        status: status,
        count: count,
        avgAge: avgAge,
        totalScore: totalScore
    }

十二、性能优化 #

12.1 使用索引 #

确保过滤条件字段有索引：

javascript

db.users.ensureHashIndex(["status"]);
db.users.ensureSkipList(["age"]);

12.2 LIMIT前置 #

aql

FOR user IN users
    LIMIT 100
    FILTER user.age > 25
    RETURN user

12.3 避免全表扫描 #

aql

FOR user IN users
    FILTER user._key IN @keys
    RETURN user

12.4 使用explain分析 #

javascript

db._explain(`
    FOR user IN users
        FILTER user.age > 25
        RETURN user
`);

十三、总结 #

基础查询要点：

FOR：遍历集合或数组
FILTER：过滤数据
SORT：排序结果
LIMIT：限制返回数量
RETURN：定义返回格式
LET：定义变量
COLLECT：聚合分组

下一步，让我们学习AQL高级查询！