数据库基础——JSON

The JSON Data Type

存储JSON文档所需的空间与LONGBLOB或LONGTEXT大致相同；存储在JSON列中的任何JSON文档的大小都限制为max_allowed_packet系统变量的值。可以使用JSON_STORAGE_SIZE( )函数获取存储JSON文档所需的空间量。

创建JSON

• JSON数组包含一组由逗号分隔的值，并且包含在[ ]中

["ade", 12, null, true, false]

• JSON对象包含由逗号分隔的键值对，并包含在{ }中，JSON对象中的键必须是字符串或者时间日期

{"k1": "value", "k2": 2}

• JSON数组和对象都运行嵌套

[99, [78, "e"], {"k1": 1, "k2": "a"}, "de"]

{"k1": 1, "k2": [10, "s"]}

常用函数

• 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_PRESERVE（）接受两个或多个JSON文档并返回组合结果：

mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}');
+-----------------------------------------------------+
| JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}') |
+-----------------------------------------------------+
| ["a", 1, {"key": "value"}]                          |
+-----------------------------------------------------+
1 row in set (0.00 sec)

• 区分大小写也适用于JSON null，true和false文字，它们必须始终以小写形式写入：
• 使用双反斜杠可防止MySQL执行转义序列处理，而是使其将字符串文字传递给存储引擎进行处理。 以刚才显示的方式插入JSON对象后，您可以通过执行简单的SELECT看到反斜杠存在于JSON列值中，如下所示：
• 要使用吉祥物作为键来查找这个特定句子，可以使用column-path操作符 - >，如下所示：

合并数组

• MySQL 8.0.3（及更高版本）支持两种合并算法，由函数JSON_MERGE_PRESERVE（）和JSON_MERGE_PATCH（）实现。 它们处理重复键的方式不同：JSON_MERGE_PRESERVE（）保留重复键的值，而JSON_MERGE_PATCH（）丢弃除最后一个值之外的所有键。 接下来的几段将解释这两个函数中的每一个如何处理JSON文档的不同组合（即对象和数组）的合并。
• 合并数组。 在组合多个数组的上下文中，数组合并为单个数组。 JSON_MERGE_PRESERVE（）通过将稍后命名的数组连接到第一个数组的末尾来完成此操作。 JSON_MERGE_PATCH（）将每个参数视为由单个元素组成的数组（因此将0作为其索引），然后应用“last duplicate key wins”逻辑以仅选择最后一个参数。 您可以比较此查询显示的结果：

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\G
*************************** 1. row ***************************
Preserve: [1, 2, "a", "b", "c", true, false]
   Patch: [true, false]

• 合并时的多个对象生成单个对象。 JSON_MERGE_PRESERVE（）通过组合数组中该键的所有唯一值来处理具有相同键的多个对象; 然后将此数组用作结果中该键的值。 JSON_MERGE_PATCH（）从左到右丢弃找到重复键的值，以便结果仅包含该键的最后一个值。 以下查询说明了重复键a的结果差异：

    ->   JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Preserve,
    ->   JSON_MERGE_PATCH('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Patch\G
*************************** 1. row ***************************
Preserve: {"a": [1, 4], "b": 2, "c": [3, 5], "d": 3}
Patch: {"a": 4, "b": 2, "c": 5, "d": 3}

搜索和修改JSON值

使用JSON路径表达式选择JSON文档中的值。

• 路径表达式对于提取JSON文档的一部分或修改JSON文档的函数很有用，以指定该文档中的操作位置。 例如，以下查询从JSON文档中提取名称为key的成员的值：

mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan"                                               |
+---------------------------------------------------------+

• 某些函数采用现有的JSON文档，以某种方式对其进行修改，并返回生成的修改后的文档。 路径表达式指示文档中的更改位置。 例如，JSON_SET（），JSON_INSERT（）和JSON_REPLACE（）函数各自采用JSON文档，以及一个或多个路径值对，这些路径值对描述了修改文档的位置和要使用的值。 这些函数在处理文档中的现有值和不存在值方面有所不同。

  mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]';
  

  • JSON_SET（）替换存在的路径的值，并为不存在的路径添加值：

  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]]      |
  +--------------------------------------------+
  

  • 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]]      |
  +-----------------------------------------------+ 
  

  • 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]]             |
  +------------------------------------------------+
  

  • 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]}]                              |
  +---------------------------------------------------+