0%

Mysql5.7新特性-JSON数据类型.md

JSON数据类型

从MySQL 5.7.8开始,MySQL支持JSON数据类型 ,可以高效访问JSON文档中的数据。与在字符串列中存储JSON格式字符串相比,数据类型具有以下优势:

  • 存储在JSON列中的JSON文档的自动验证 。错误的JSON数据无法插入
  • 优化的存储格式。可以直接用SQL语句查询JSON文档中的内容。

JSON存储要求

通常,JSON列的存储要求与LONGBLOB或 LONGTEXT列的存储要求大致相同; 也就是说,JSON文档占用的空间与存储在其中一种类型的列中的文档字符串表示形式大致相同。

但是,存储在JSON文档中的各个值的二进制编码(包括查找所需的元数据和字典)会产生开销。例如,存储在JSON文档中的字符串需要4到10个字节的额外存储空间,具体取决于字符串的长度以及存储它的对象或数组的大小。

此外,MySQL对存储在JSON列中的任何JSON文档的大小施加限制,使得它不能大于值的任何大小 max_allowed_packet。

JSON列不能有非NULL默认值。JSON列,如其他二进制类型的列,不直接索引; 相反,可以在生成的列上创建索引,该列从列中提取标量值JSON。

基本函数

创建JSON值

  • JSON_ARRAY([val[, val] …])
    计算(可能为空)值列表并返回包含这些值的JSON数组。

mysql> SELECT JSON_ARRAY(1, “abc”, NULL, TRUE, CURTIME());
+———————————————+
| JSON_ARRAY(1, “abc”, NULL, TRUE, CURTIME()) |
+———————————————+
| [1, “abc”, null, true, “11:30:24.000000”] |
+———————————————+

  • JSON_OBJECT([key, val[, key, val] …])

    计算键值对(可能为空)并返回包含这些对的JSON对象。如果任何键名称NULL或参数数量为奇数,则会发生错误。

mysql> SELECT JSON_OBJECT(‘id’, 87, ‘name’, ‘carrot’);
+—————————————–+
| JSON_OBJECT(‘id’, 87, ‘name’, ‘carrot’) |
+—————————————–+
| {“id”: 87, “name”: “carrot”} |
+—————————————–+

  • JSON_QUOTE(string)

通过用双引号字符包装并转义内部引号和其他字符,然后将结果作为utf8mb4字符串返回,将字符串引用为JSON值 。如果参数是NULL,则 返回NULL。

mysql> SELECT JSON_QUOTE(‘null’), JSON_QUOTE(‘“null”‘);
+——————–+———————-+
| JSON_QUOTE(‘null’) | JSON_QUOTE(‘“null”‘) |
+——————–+———————-+
| “null” | “\”null\”” |
+——————–+———————-+
mysql> SELECT JSON_QUOTE(‘[1, 2, 3]’);
+————————-+
| JSON_QUOTE(‘[1, 2, 3]’) |
+————————-+
| “[1, 2, 3]” |
+————————-+

在MySQL中,JSON值被写为字符串。
MySQL会去解析设置为JSON类型的字符串,如果插入的是错误的JSON,这样会导致插入失败

如以下示例所示:

  • JSON 如果值是有效的JSON值,则 尝试将值插入列成功,但如果不是,则尝试失败:

mysql> CREATE TABLE t1 (jdoc JSON);
Query OK, 0 rows affected (0.20 sec)
mysql> INSERT INTO t1 VALUES(‘{“key1”: “value1”, “key2”: “value2”}’);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO t1 VALUES(‘[1, 2,’);
ERROR 3140 (22032) at line 2: Invalid JSON text:
“Invalid value.” at position 6 in value (or column) ‘[1, 2,’.

  • JSON_TYPE()函数需要一个JSON参数并尝试将其解析为JSON值。如果值有效,则返回值的JSON类型,否则产生错误:

mysql> SELECT JSON_TYPE(‘[“a”, “b”, 1]’);
+—————————-+
| JSON_TYPE(‘[“a”, “b”, 1]’) |
+—————————-+
| ARRAY |
+—————————-+
mysql> SELECT JSON_TYPE(‘“hello”‘);
+———————-+
| JSON_TYPE(‘“hello”‘) |
+———————-+
| STRING |
+———————-+
mysql> SELECT JSON_TYPE(‘hello’);
ERROR 3146 (22032): Invalid data type for JSON data in argument 1
to function json_type; a JSON string or JSON type is required.

  • JSON_ARRAY()获取(可能为空)值列表并返回包含这些值的JSON数组:

mysql> SELECT JSON_ARRAY(‘a’, 1, NOW());
+—————————————-+
| JSON_ARRAY(‘a’, 1, NOW()) |
+—————————————-+
| [“a”, 1, “2015-07-27 09:43:47.000000”] |
+—————————————-+

  • JSON_OBJECT() 获取(可能为空)键值对列表并返回包含这些对的JSON对象:

mysql> SELECT JSON_OBJECT(‘key1’, 1, ‘key2’, ‘abc’);
+—————————————+
| JSON_OBJECT(‘key1’, 1, ‘key2’, ‘abc’) |
+—————————————+
| {“key1”: 1, “key2”: “abc”} |
+—————————————+

  • JSON_MERGE() 获取两个或多个JSON文档并返回组合结果:

mysql> SELECT JSON_MERGE(‘[“a”, 1]’, ‘{“key”: “value”}’);
+——————————————–+
| JSON_MERGE(‘[“a”, 1]’, ‘{“key”: “value”}’) |
+——————————————–+
| [“a”, 1, {“key”: “value”}] |
+——————————————–+

搜索和修改JSON值

路径表达式对于提取JSON文档的一部分或修改JSON文档的函数很有用,以指定该文档中的操作位置。
例如,以下查询从JSON文档中提取具有name键的成员的值:

mysql> SELECT JSON_EXTRACT(‘{“id”: 14, “name”: “Aztalan”}’,’$.name’);

+———————————————————+
| JSON_EXTRACT(‘{“id”: 14, “name”: “Aztalan”}’, ‘$.name’) |
+———————————————————+
| “Aztalan” |
+———————————————————+

路径语法使用前导$字符来表示正在选择的JSON文档, 后面跟着对应的选择器。

  • 点后面跟的是JSON对象中指定的键。
  • [N]附加到路径后面path后面,选择一个数组中位置N,数组位置是从零开始的整数。如果path不是数组,则[0]选取的整个对象

mysql> SELECT JSON_SET(‘“x”‘, ‘$[0]’, ‘a’);
+——————————+
| JSON_SET(‘“x”‘, ‘$[0]’, ‘a’) |
+——————————+
| “a” |
+——————————+
1 row in set (0.00 sec)
mysql> SELECT JSON_SET(‘{“id”: 14, “name”: “Aztalan”}’, ‘$[1]’, ‘ccc’);
+———————————————————-+
| JSON_SET(‘{“id”: 14, “name”: “Aztalan”}’, ‘$[1]’, ‘ccc’) |
+———————————————————-+
| [{“id”: 14, “name”: “Aztalan”}, “ccc”] |
+———————————————————-+
1 row in set (0.00 sec)

  • 路径可以包含*或 **通配符:

    • .[*] JSON对象中所有成员的值。
    • [*] JSON数组中所有元素的值。
    • prefix**suffix 计算所有以命名前缀开头并以命名后缀结尾的路径。
  • 文档中不存在的路径的计算结果为NULL。

让我们$用三个元素来引用这个JSON数组:

1
[3, {"a": [5, 6], "b": 10}, [99, 100]]
  • $[0]计算结果为3。
  • $[1]计算结果为{“a”: [5, 6], “b”: 10}。
  • $[2]计算结果为[99, 100]。
  • $[3]计算结果为NULL (它指的是第四个不存在的数组元素)。
  • $[1].a计算结果为[5, 6]。
  • $[1].a[1]计算结果为 6。
  • $[1].b计算结果为 10。
  • $[2][0]计算结果为 99。

如果路径表达式中的键名称不合法,则必须引号对键进行引用。

1
{"a fish": "shark", "a bird": "sparrow"}
  • $.”a fish”计算结果为 shark。
  • $.”a bird”计算结果为 sparrow。

计算多个路径数组:

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

mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*') |
+---------------------------------------------------------+
| [1, 2, [3, 4, 5]] |
+---------------------------------------------------------+
mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]');
+------------------------------------------------------------+
| JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]') |
+------------------------------------------------------------+
| [3, 4, 5] |
+------------------------------------------------------------+
mysql> SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |
+---------------------------------------------------------+
| [1, 2] |
+---------------------------------------------------------+
  • JSON_CONTAINS(target, candidate[, path])

通过返回1或0来指示给定的candidate来标示JSON文档是否包含在target的JSON文档中,或者如果提供了path参数检查是否在目标内的特定路径中找到候选项。

如果target或candidate不是有效的JSON文档,或者如果path参数不是一个有效的路径表达式或包含一个 *或**通配符则会报错。

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
28
29

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SET @j2 = '1';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
| 1 |
+-------------------------------+
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.b') |
+-------------------------------+
| 0 |
+-------------------------------+

mysql> SET @j2 = '{"d": 4}';
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.a') |
+-------------------------------+
| 0 |
+-------------------------------+
mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c');
+-------------------------------+
| JSON_CONTAINS(@j, @j2, '$.c') |
+-------------------------------+
| 1 |
+-------------------------------+
  • JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] …)

返回0或1以指示JSON文档是否包含给定路径或路径的数据。

  • ‘one’:如果文档中至少存在一个路径,则为1,否则为0。

  • ‘all’:如果文档中存在所有路径,则为1,否则为0。

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

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
+---------------------------------------------+
| 0 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |
+----------------------------------------+
| 1 |
+----------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |
+----------------------------------------+
| 0 |
+----------------------------------------+
  • JSON_EXTRACT(json_doc, path[, path] …)

返回JSON文档中的数据,该文档从path 参数匹配的文档部分中选择。

mysql> SELECT JSON_EXTRACT(‘[10, 20, [30, 40]]’, ‘$[1]’);
+——————————————–+
| JSON_EXTRACT(‘[10, 20, [30, 40]]’, ‘$[1]’) |
+——————————————–+
| 20 |
+——————————————–+
mysql> SELECT JSON_EXTRACT(‘[10, 20, [30, 40]]’, ‘$[1]’, ‘$[0]’);
+—————————————————-+
| JSON_EXTRACT(‘[10, 20, [30, 40]]’, ‘$[1]’, ‘$[0]’) |
+—————————————————-+
| [20, 10] |
+—————————————————-+
mysql> SELECT JSON_EXTRACT(‘[10, 20, [30, 40]]’, ‘$[2][]’);
+———————————————–+
| JSON_EXTRACT(‘[10, 20, [30, 40]]’, ‘$[2][
]’) |
+———————————————–+
| [30, 40] |
+———————————————–+