Mysql Json相关使用

1、创建带虚拟列的数据库表

CREATE TABLE use_info (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `detail` JSON DEFAULT NULL,
  `name` VARCHAR(6) GENERATED ALWAYS AS (json_unquote(json_extract(`detail`, '$.name'))) VIRTUAL,
  PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARACTER SET = utf8;

2、插入记录

INSERT INTO `use_info` (`detail`) VALUES ('{\"age\": 25, \"sex\": \"男\", \"name\": \"张三\"}');

3、添加虚拟列

ALTER TABLE use_info ADD COLUMN `age` INT(3) GENERATED ALWAYS AS (json_unquote(json_extract(`detail`, '$.age'))) VIRTUAL;

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

SELECT JSON_TYPE('{"age": 25, "sex": "男", "name": "张三"}');

若不是JSON参数，则会出现如下错误信息：

Invalid JSON text in argument 1 to function json_type: "Invalid value."

(2)JSON_ARRAY()
JSON_ARRAY()接受（可能为空）值列表，并返回包含这些值的JSON数组.

SELECT JSON_ARRAY('你好', 70, NOW());

(3)JSON_OBJECT()
JSON_OBJECT() 接受键值对的列表（可能为空），并返回包含这些对的JSON对象.

 SELECT JSON_OBJECT('name', '李四', 'age', 25);

(4)JSON_MERGE()
JSON_MERGE() 接受两个或多个JSON文档并返回合并的结果.

SELECT JSON_MERGE('{"age": 25, "name": "李四"}', '{"age": 25, "sex": "男", "name": "张三"}');

(5)JSON_EXTRACT()
路径表达式对于提取JSON文档的一部分或修改JSON文档以指定在该文档中的何处进行操作的函数很有用.

SELECT JSON_EXTRACT(detail, '$.name') FROM use_info;

注：语法使用前导$字符表示所考虑的JSON文档，“.”之后表示需要的key名称。
我们也可以使用通配符获取所有的值：

SELECT JSON_EXTRACT(detail, '$.*') FROM use_info;

JSON_EXTRACT也可以使用如下方式替换：
查找json内的某个键的值

SELECT `detail`-> "$.name" FROM use_info;

若不想要结果中包含引号，可以这样：

SELECT `detail`->> "$.name" FROM use_info;

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

SELECT JSON_SET(detail, '$[1]', '123') FROM use_info;

注：JSON_SET的第一个参数为需要修改的json字段，第二个为修改的路径，从0开始，第三个为修改的内容。如上例子就是把json中1位置的NULL替换成了"123"。
例如：

[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。

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

SELECT JSON_INSERT(detail, '$[0]', "你好", '$[2]', "是的，很好") FROM use_info;

注：0处的内容并没有被替换掉。

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

SELECT JSON_REPLACE(detail, '$[0]', "你好", '$[2]', "是的，很好") FROM use_info;

注：0处内容被替换成"你好", 而二处由于是新值，所以被忽略了。

(9)JSON_UNQUOTE()
JSON_UNQUOTE去除json字符串的引号，将值转成string类型.

SELECT JSON_UNQUOTE(JSON_EXTRACT(detail, '$.name')) FROM use_info;