MySQL 5.7 JSON操作

JSON是一种轻量级的数据交换格式，易读并且易编写。对机器来说易于解析和生成，并且会减少网络带宽的传输，在现在开发中非常流行。 MySQL 5.7 自 2015年12月发布已经过去5年多了，现在回头补学还来得及吧。

中文手册：https://www.docs4dev.com/docs/zh/mysql/5.7/reference/json.html
1、JSON长什么样?
(1)JSON 数组包含用逗号分隔并包含在[和]字符内的值的列 table：

["abc", 10, null, true, false]

(2)JSON 对象包含一组键值对，以逗号分隔并包含在{和}字符内：

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

(3)如示例所示，JSON 数组和对象可以包含字符串或数字的标量值，JSON 空 Literals 或 JSON 布尔值 true 或 falseLiterals。 JSON 对象中的键必须是字符串。还允许使用时间(日期，时间或日期时间)标量值：

["12:18:29.000000", "2015-07-29", "2015-07-29 12:18:29.000000"]

(4)JSON 数组元素和 JSON 对象键值中允许嵌套：

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}

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

2、JSON 值的规范化，合并和自动包装
JSON_OBJECT()具有与文档中较早发现的键重复的键的成员将被丢弃(即使值不同)；

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');

结果：{“key1”: 1, “key2”: “abc”}
JSON_MERGE()通过将稍后命名的数组连接到第一个数组的末尾，将数组合并为单个数组。

SELECT JSON_MERGE('[1, 2]', '["a", "b"]', '[true, false]');

结果： [1, 2, “a”, “b”, true, false]

3、搜索和修改 JSON 值
JSON_EXTRACT：路径 table 达式对于提取 JSON 文档的一部分或修改 JSON 文档以指定在该文档中的何处进行操作的函数很有用

SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');

结果： “Aztalan”
补充使用：路径可以包含或**通配符：
.[]求值为 JSON 对象中所有成员的值。
[*]计算 JSON 数组中所有元素的值。
prefix**suffix计算所有以命名前缀开头并以命名后缀结尾的路径。
文档中不存在的路径(评估为不存在的数据)评估为NULL。

JSON_SET()替换现有路径的值，并添加不存在的路径的值

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

SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);

结果：[“a”, {“b”: [1, false]}, [10, 20, 2]]

JSON_INSERT()添加新值，但不替换现有值

SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2)

结果：[“a”, {“b”: [true, false]}, [10, 20, 2]]

JSON_REPLACE()替换现有值，并忽略新值

SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2);

结果：[“a”, {“b”: [1, false]}, [10, 20]]

JSON_REMOVE()接收 JSON 文档和一个或多个指定要从该文档中删除的值的路径

 SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');

结果： [“a”, {“b”: [true]}]

4、JSON 值的比较和排序
可以使用=，<，<=，>，>=，<>，!=和<=>运算符比较 JSON 值；
规则：对于具有相同优先级的 JSON 值，比较规则是特定于类型的：

BLOB
比较两个值的第一个* N 字节，其中 N 是较短值中的字节数。如果两个值的前 N *字节相同，则较短的值将在较长的值之前排序。

BIT
与BLOB相同的规则。

OPAQUE
与BLOB相同的规则。 OPAQUE值是未归类为其他类型之一的值。

DATETIME
table 示较早时间点的值先于 table 示较晚时间点的值排序。如果两个值最初分别来自 MySQL DATETIME和TIMESTAMP类型，则如果它们 table 示相同的时间点，则它们相等。

TIME
两个时间值中较小的一个先于较大的一个。

DATE
较早的日期在较新的日期之前排序。

ARRAY
如果两个 JSON 数组的长度相同并且数组中对应位置的值相等，则它们相等。

如果数组不相等，则它们的 Sequences 由存在差异的第一个位置的元素确定。在该位置具有较小值的数组将首先排序。如果较短数组的所有值都等于较长数组中的相应值，则首先对较短数组进行排序。