The JSON Data Type

2019-03-31 本文已影响0人夜阑w

一、创建JSON值

JSON数组：["abc", 10, null, true, false]
JSON对象：{"k1": "value", "k2": 10}
JSON数组和JSON对象键值中允许嵌套：[99, ["hot", "cold"]], {"k1": "value", "k2": [10, 20]}

创建有JSON字段的表，并向表中的JSON列插入数据，如果数据是有效的JSON值，则插入成功，否则会失败。例：

mysql>  CREATE TABLE t_json(jdoc JSON);
Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO t_json VALUES('{"key1": "value1", "key2": "value2"}');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t_json VALUES('[1,2,');
ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 5 in value for column 't_json.jdoc'.

JSON_TYPE()可以返回JSON文档的具体类型，例：

mysql> SELECT JSON_TYPE('["a", "b", 1]');
+----------------------------+
| JSON_TYPE('["a", "b", 1]') |
+----------------------------+
| ARRAY                      |
+----------------------------+
1 row in set (0.01 sec)

JSON_VALID()判断是否为有效的JSON类型，例：

mysql> SELECT JSON_VALID('{"a": 1}');
+------------------------+
| JSON_VALID('{"a": 1}') |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_VALID('null');
+--------------------+
| JSON_VALID('null') |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

JSON_ARRAY()生成一个包含指定元素的JSON数组，例：

mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW())              |
+----------------------------------------+
| ["a", 1, "2019-03-31 17:01:41.000000"] |
+----------------------------------------+
1 row in set (0.01 sec)

JSON_OBJECT()生成JSON对象，下面会有详细例子。

二、JSON值的规范化，合并和自动包装

1. 规范化

解析字符串并发现它是有效的JSON类型时，会进行规范化。当前后存在相同的键名称时，键值会发生覆盖的情况，即保留重复键的最后一个键值。如下所示：

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
+------------------------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def') |
+------------------------------------------------------+
| {"key1": "def", "key2": "abc"}                       |
+------------------------------------------------------+
1 row in set (0.02 sec)

向JSON列中插入数据时也会发生同样的情况，例：

mysql> INSERT INTO t1 VALUES
    -> ('{"x":17,"x":"red"}');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1;
+--------------+
| jdoc         |
+--------------+
| {"x": "red"} |
+--------------+
1 row in set (0.00 sec)

2. 合并JSON值

可以通过JSON_MERGE_PRESERVE()和JSON_MERGE_PATCH()函数对JSON值进行合并。它们的区别主要是对于重复键的处理方式不同，JSON_MERGE_PRESERVE()会保留重复键的值，而JSON_MERGE_PATCH()会丢弃除最后一个值之外的其它键。

合并数组是将多个数据合并为一个。JSON_MERGE_PRESERVE()通过将后面的数据连接到第一个数组的末尾来实现。JSON_MERGE_PATCH()将每个数组视为索引为0的单个元素，最后只选择了最后一个参数。例：

mysql> SELECT
    -> JSON_MERGE_PRESERVE('[1,2]','["a","b","c"]','[true,false]') AS Preserve,
    -> JSON_MERGE_PATCH('[1,2]','["a","b","c"]','[true,false]') AS Patch;
+------------------------------------+---------------+
| Preserve                           | Patch         |
+------------------------------------+---------------+
| [1, 2, "a", "b", "c", true, false] | [true, false] |
+------------------------------------+---------------+
1 row in set (0.01 sec)

合并对象是将多个对象合并为一个对象。JSON_MERGE_PRESERVE()通过将重复键的不同值组合为一个数组来处理多个对象，最终将该数组作为键值。JSON_MERGE_PATCH()只保留重复键的最后一个值。例：

mysql> SELECT
    -> JSON_MERGE_PRESERVE('{"a":1,"b":2}','{"c":3,"a":4}','{"c":5,"d":3}') AS Preserve,
    -> JSON_MERGE_PATCH('{"a":1,"b":2}','{"c":3,"a":4}','{"c":5,"d":3}') AS Patch;
+--------------------------------------------+----------------------------------+
| Preserve                                   | Patch                            |
+--------------------------------------------+----------------------------------+
| {"a": [1, 4], "b": 2, "c": [3, 5], "d": 3} | {"a": 4, "b": 2, "c": 5, "d": 3} |
+--------------------------------------------+----------------------------------+
1 row in set (0.00 sec)

3. 自动包装

非数组值在需要时会自动包装为一个数组，JSON_MERGE_PRESERVE()和JSON_MERGE_PATCH()的区别如下所示：

mysql> SELECT
    -> JSON_MERGE_PRESERVE('1','2') AS Preserve,
    -> JSON_MERGE_PATCH('1','2') AS Patch;
+----------+-------+
| Preserve | Patch |
+----------+-------+
| [1, 2]   | 2     |
+----------+-------+
1 row in set (0.00 sec)

数组和对象通过将对象自动包装为一个数组然后合并数组来进行合并，或者保留最后一个重复键的值，这是JSON_MERGE_PRESERVE()和JSON_MERGE_PATCH()的区别，例：

mysql> SELECT
    -> JSON_MERGE_PRESERVE('[10,20]','{"a":"x"}') AS Preserve,
    -> JSON_MERGE_PATCH('[10,20]','{"a":"x"}') AS Patch;
+----------------------+------------+
| Preserve             | Patch      |
+----------------------+------------+
| [10, 20, {"a": "x"}] | {"a": "x"} |
+----------------------+------------+
1 row in set (0.00 sec)

三、搜索和修改JSON值

1. 搜索

JSON中的路径表达式可以选择JSON文档中的值。
例如选择JSON对象中的一个键值：

mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan"                                               |
+---------------------------------------------------------+
1 row in set (0.01 sec)

路径语法使用前导字符$来表示当前的JSON文档，可选地后跟选择器。

一个.后面跟着键的名字表示一个对象中给定键的值。
[N]在路径中用于选择一个数组中处于位置位置N的值，位置从0开始。
[M,N]表示数组中从M开始，到N结束的一段范围内的子数组。
路径中可以包含*或**通配符。其中.[*]可以得到JSON对象中所有成员的值，[*]可以得到JSON数组中所有元素的值。

搜索JSON对象，例：

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]]                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

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]                                               |
+---------------------------------------------------------+
1 row in set (0.00 sec)

**的使用，可以搜索得到多个路径中对应的值，并组成一个数组，例：

mysql> SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2}}', '$**.b') |
+---------------------------------------------------------+
| [1, 2]                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

搜索JSON数组，例：

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');
+----------------------------------------------+
| JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]') |
+----------------------------------------------+
| [2, 3, 4]                                    |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-2 to last-1]');
+--------------------------------------------------------+
| JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-2 to last-1]') |
+--------------------------------------------------------+
| [3, 4]                                                 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

可以使用column->path代替JSON_EXTRACT(column, path)。

2. 修改

可以通过一些函数以某种方式对JSON文档进行修改，并返回生成的修改后的文档。路径表达式指示文档中的更改位置。

一个例子：

mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]';
Query OK, 0 rows affected (0.01 sec)

JSON_SET()替换存在路径的值，并添加不存在路径的值，例子如下。其中$[1].b[0]指向true，将其替换为1；$[2][2]不存在，为其增加值2。

mysql> SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+--------------------------------------------+
| JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+--------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20, 2]]      |
+--------------------------------------------+
1 row in set (0.01 sec)

JSON_INSERT()添加新的值但不会替换已存在的值，例：

mysql> SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+-----------------------------------------------+
| JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+-----------------------------------------------+
| ["a", {"b": [true, false]}, [10, 20, 2]]      |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_INSERT(@j, '$[1].b[2]', 1, '$[2][2]', 2);
+-----------------------------------------------+
| JSON_INSERT(@j, '$[1].b[2]', 1, '$[2][2]', 2) |
+-----------------------------------------------+
| ["a", {"b": [true, false, 1]}, [10, 20, 2]]   |
+-----------------------------------------------+
1 row in set (0.00 sec)

JSON_REPLACE()会替换已存在的值忽略新值，例：

mysql> SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+------------------------------------------------+
| JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+------------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20]]             |
+------------------------------------------------+
1 row in set (0.00 sec)

JSON_REMOVE()接受一个JSON文档和一个或多个指定要从文档中删除的值的路径。返回值是原始文档减去文档中存在的路径所选择的值，例：

mysql> SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
+---------------------------------------------------+
| JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]') |
+---------------------------------------------------+
| ["a", {"b": [true]}]                              |
+---------------------------------------------------+
1 row in set (0.00 sec)

其中，$[2]指向[10,20]并将其删除，$[1].b[1]指向false并将其删除，删除false后$[1].b[1]不再存在。