N1QL使用方法

# 基本的选择语句

SELECT 'Hello World' AS Greeting

结果：

{
  "results": [
    {
      "Greeting": "Hello World"
    }
  ]
}

1
2
3
4
5
6
7

Couchbase的N1QL选择结果由一个JSON字典给出，字典最外层是result字段，字段值是一个JSON数组，存储了所有的结果。

# 基础

一个叫tutorial的文档库有如下6个json文档：

{
    "age": 46,
    "children": [
        {"age": 17,"fname": "Aiden","gender": "m"},
        {"age": 2,"fname": "Bill","gender": "f"}
    ],
    "email": "dave@gmail.com",
    "fname": "Dave",
    "hobbies": ["golf","surfing"],
    "lname": "Smith",
    "relation": "friend",
    "title": "Mr.",
    "type": "contact"
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14

{
    "age": 46,
    "children": [
        {"age": 17,"fname": "Xena","gender": "f"},
        {"age": 2,"fname": "Yuri","gender": "m"}
    ],
    "email": "earl@gmail.com",
    "fname": "Earl",
    "hobbies": ["surfing"],
    "lname": "Johnson",
    "relation": "friend",
    "title": "Mr.",
    "type": "contact"
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14

{
    "age": 18,
    "children": null,
    "email": "fred@gmail.com",
    "fname": "Fred",
    "hobbies": ["golf","surfing"],
    "lname": "Jackson",
    "relation": "coworker",
    "title": "Mr.",
    "type": "contact"
}

1
2
3
4
5
6
7
8
9
10
11

{
    "age": 20,
    "email": "harry@yahoo.com",
    "fname": "Harry",
    "lname": "Jackson",
    "relation": "parent",
    "title": "Mr.",
    "type": "contact"
}

1
2
3
4
5
6
7
8
9

{
    "age": 56,
    "children": [
        {"age": 17,"fname": "Abama","gender": "m"},
        {"age": 21,"fname": "Bebama","gender": "m"}
    ],
    "email": "ian@gmail.com",
    "fname": "Ian",
    "hobbies": ["golf","surfing"],
    "lname": "Taylor",
    "relation": "cousin",
    "title": "Mr.",
    "type": "contact"
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14

{
    "age": 40,
    "contacts": [
        {"fname": "Fred"},
        {"fname": "Sheela"}
    ],
    "email": "jane@gmail.com",
    "fname": "Jane",
    "lname": "Edwards",
    "relation": "cousin",
    "title": "Mrs.",
    "type": "contact"
}

1
2
3
4
5
6
7
8
9
10
11
12
13

# 条件选择和从某个文档库中选择

# WHERE

从tutorial文档库中选出所有fname为Ian的文档内容。

SELECT * FROM tutorial WHERE fname = 'Ian'

返回第5个JSON文档：

{
  "results": [
      <第5个JSON文档的内容>
  ]
}

1
2
3
4
5

从tutorial文档库中选出所有fname为Dave的文档中children字段的第一项的fname，并命名为child_name。

SELECT children[0].fname AS child_name FROM tutorial WHERE fname='Dave'

返回结果是第一个JSON文档中children字段的第一项的fname字段值：

{
  "results": [
    {
      "child_name": "Aiden"
    }
  ]
}

1
2
3
4
5
6
7

# LIKE

同样的，N1QL中也有LIKE语句：

SELECT fname, email FROM tutorial WHERE email LIKE '%@yahoo.com'

选出所有用雅虎邮箱的文档的fname和email。

# AND

和一般的SQL一样，不多说：

SELECT fname, email, children
    FROM tutorial
        WHERE ARRAY_LENGTH(children) > 0 AND email LIKE '%@gmail.com'

1
2
3

# 选择加计算

和一般SQL的计算一样，不用多说，当被计算的字段是数值类型时可用：

SELECT fname AS name_dog, age, age/7 AS age_dog_years FROM tutorial WHERE fname = 'Dave'

如果被计算的字段不是数值类型，那就会返回null。

# 函数

和一般的SQL函数用法一样，不用多说：

SELECT fname, age, ROUND(age/7) AS age_dog_years FROM tutorial WHERE fname = 'Dave'

聚合函数也是一样：

SELECT COUNT(*) AS count FROM tutorial

# 分组

说到聚合函数就要说GROUP BY分组查询，N1QL和SQL里面的也是一样：

SELECT relation, COUNT(*) AS count FROM tutorial GROUP BY relation

{
  "results": [
    {"count": 1,"relation": "parent"},
    {"count": 2,"relation": "cousin"},
    {"count": 2,"relation": "friend"},
    {"count": 1,"relation": "coworker"}
  ]
}

1
2
3
4
5
6
7
8

# 选择分组

在分组聚合后取部分查询结果。比如在上面那个查亲属人数的语句基础上加一个HAVING子句：

SELECT relation, COUNT(*) AS count FROM tutorial GROUP BY relation HAVING COUNT(*) > 1

将只从亲属计数中返回人员总数大于1的查询结果：

{
  "results": [
    {"count": 2,"relation": "cousin"},
    {"count": 2,"relation": "friend"}
  ]
}

1
2
3
4
5
6

# 字符串连接

N1QL的字符串不是像SQL中的连接函数，而是用||符号：

SELECT fname || " " || lname AS full_name FROM tutorial

# DISTINCT

和SQL一样，N1QL也有DISTINCT：

SELECT DISTINCT relation FROM tutorial

返回所有的relation字段：

{
  "results": [
    {"relation": "friend"},
    {"relation": "coworker"},
    {"relation": "parent"},
    {"relation": "cousin"}
  ]
}

1
2
3
4
5
6
7
8

# 找空值

字段不存在和字段显式地指定为null都算空值：

SELECT fname, children FROM tutorial WHERE children IS NULL

返回children字段不存在或为null的文档的fname和children：

{
  "results": [
    {
      "children": null,
      "fname": "Fred"
    }
  ]
}

1
2
3
4
5
6
7
8

# 排序和指定个数

和一般的SQL中排序一样，不多说：

SELECT fname, age FROM tutorial ORDER BY age LIMIT 2

# 跳过

OFFSET用于跳过结果，比如上面的N1QL语句加上OFFSET之后：

SELECT fname, age FROM tutorial ORDER BY age LIMIT 2 OFFSET 4

则返回按age排序，跳过前面4个结果，返回第5和6名。

# 进阶

# 返回数据库的元数据

Document databases such as Couchbase often store meta-data about a document outside of the document.

比如数据库中的文档ID是典型的数据库元数据：

SELECT META(tutorial) AS meta FROM tutorial

{
  "results": [
    {"meta": {"id": "dave"}},
    {"meta": {"id": "earl"}},
    {"meta": {"id": "fred"}},
    {"meta": {"id": "harry"}},
    {"meta": {"id": "ian"}},
    {"meta": {"id": "jane"}}
  ]
}

1
2
3
4
5
6
7
8
9
10

# 复合条件语句

复合条件语句的作用是判断一个JSON数组格式字段的所有值，有两种：

ANY [循环变量] IN [数组] SATISFIES [条件] END
EVERY [循环变量] IN [数组] SATISFIES [条件] END

顾名思义，ANY是指只要有一项满足就是true，EVERY必须要所有条件满足才返回true。

# ANY

选出家里有至少一个孩子在十岁以上的家庭的fname：

SELECT fname
    FROM tutorial
        WHERE ANY child IN tutorial.children SATISFIES child.age > 10 END

1
2
3

# EVERY

选出家里有全部孩子都在十岁以上的家庭的fname：

SELECT fname
    FROM tutorial
        WHERE EVERY child IN tutorial.children SATISFIES child.age > 10 END

1
2
3

# USE KEYS []

这个语句的功能和WHERE一样，都是按照某个字段找文档，但是WHERE是按照文档内的字段找文档，而USE KEYS []使用文档元数据中的文档ID找。显然，找文档ID比找文章内的值快：

SELECT * FROM tutorial USE KEYS ["dave", "ian"]

按照前面的META的结果，这个语句会返回第一个和第五个文档。

# 数组切片

N1QL中的数组切片和python一样，不多说，看看就懂：

SELECT children[0:2] FROM tutorial

返回每个文档的children字段的前两个值，如果文档的children字段值是null，那么返回字段值也是null；如果原文档没有children字段，则返回空字典{}：

{
  "results": [
    {
      "$1": [
        {"age": 17,"fname": "Aiden","gender": "m"},
        {"age": 2,"fname": "Bill","gender": "f"}
      ]
    },
    {
      "$1": [
        {"age": 17,"fname": "Xena","gender": "f"},
        {"age": 2,"fname": "Yuri","gender": "m"}
      ]
    },
    {
      "$1": null
    },
    {},
    {
      "$1": [
        {"age": 17,"fname": "Abama","gender": "m"},
        {"age": 21,"fname": "Bebama","gender": "m"}
      ]
    },
    {}
  ]
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

# IS NOT MISSING

IS NOT MISSING用于判断字段值是否存在，如果不存在则不返回。比如当上面的数组切片查询加上的这个子句之后：

SELECT children[0:2] FROM tutorial WHERE children[0:2] IS NOT MISSING

返回值中就不会有因为字段不存在而返回的空字典{}了。

# `ARRAY`循环生成

N1QL中的ARRAY和python中的列表推导式很像：

SELECT fname AS parent_name,
ARRAY child.fname FOR child IN tutorial.children END AS child_names
FROM tutorial WHERE children IS NOT NULL

1
2
3

对比python中的列表推导式：

child_names = [child.fname for child in tutorial.children]

一看就懂，不多说。

# 高级

# 表连接

和SQL一样，按照结果中的数据进行连接。具体来说，Couchbase的表连接就是在一个文档库中找到特定字段为特定值的文档，然后和另一个文档连在一起：

SELECT * FROM users_with_orders usr
    JOIN orders_with_users orders
        ON KEYS ARRAY s.order_id FOR s IN usr.shipped_order_history END

1
2
3

连接文档库users_with_orders和orders_with_users，并分别以usr和orders作为其别名
orders_with_users文档库中文文档的ID为users_with_orders库中文档的shipped_order_history[i].order_id的值
连接时，对文档库users_with_orders每个文档的shipped_order_history字段中的每一项，都取其order_id字段，以此为ID在orders_with_users文档库中查找文档
对文档库users_with_orders每个文档，将orders_with_users文档库中中查找到的对应文档与之组合为结果的一项，其字段名为各自文档库的别名：

{
  "results": [
    {
        "usr": <users_with_orders中对应数据>,
        "orders":<orders_with_users中对应数据>
    },
    ...
  ]
}

1
2
3
4
5
6
7
8
9

# LEFT JOIN

上面那个语句的连接操作没有LEFT JOIN子句，这时，如果users_with_orders中有某个文档在orders_with_users中没有对应的文库可以连接，那么这个文档就不会出现在结果中。而如果用了LEFT JOIN子句：

SELECT * FROM users_with_orders usr
    JOIN orders_with_users orders
        ON KEYS ARRAY s.order_id FOR s IN usr.shipped_order_history END

1
2
3

那么在orders_with_users中没有对应的文库可以连接的users_with_orders文档也会出现在结果中，只是没有orders_with_users中的字段。

（LEFT JOIN将子句左边文档库的没有连接的项显示在结果中）

# NEST

NEST和JOIN的功能完全一样，不一样的只是输出。

对于子句左边的每一项，JOIN将子句右边的连接结果与之一一相连，如果左边有一项和右边有3项可以相连，那么结果中就会有3个结果，这3个结果中左边的这个结果会重复3次。比如上面的那句N1QL可能输出：

{
  "results": [
    {
        "usr": <usr数据1>,
        "orders":<orders数据1>
    },
    {
        "usr": <usr数据1>,
        "orders":<orders数据2>
    },
    {
        "usr": <usr数据1>,
        "orders":<orders数据3>
    }
  ]
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

而如果改成NEST：

SELECT * FROM users_with_orders usr
    NEST orders_with_users orders
        ON KEYS ARRAY s.order_id FOR s IN usr.shipped_order_history END

1
2
3

那么右边文档库orders_with_users中的那三个数据会被“nest”到一个数组里面，就像这样：

{
  "results": [
    {
        "usr": <usr数据1>,
        "orders":[<orders数据1>,<orders数据2>,<orders数据3>]
    }
  ]
}

1
2
3
4
5
6
7
8

结果的长度大大减小。

# LEFT NEST

NEST和LEFT NEST的区别就像JOIN和LEFT JOIN的区别一样，都是把左边没有连接的项也放在结果中，不再赘述。

Yin的笔记本

N1QL使用方法

# 基本的选择语句

# 基础

# 条件选择和从某个文档库中选择

# WHERE

# LIKE

# AND

# 选择加计算

# 函数

# 分组

# 选择分组

# 字符串连接

# DISTINCT

# 找空值

# 排序和指定个数

# 跳过

# 进阶

# 返回数据库的元数据

# 复合条件语句

# ANY

# EVERY

# USE KEYS []

# 数组切片

# IS NOT MISSING

# ARRAY循环生成

# 高级

# 表连接

# LEFT JOIN

# NEST

# LEFT NEST

# `ARRAY`循环生成