MySQL中对于操作json数据的函数的简单使用

MySQL5.7.8之后添加了对json数据格式的数据进行存储的功能，也添加了一些操作的函数，下面为我个人在学习过程中结合MySQL官网的教程记录的笔记，如有错漏，欢迎大家留言一起讨论，不喜勿喷。 

-- 创建一张表，名为t1，里面的字段为jdoc，数据类型为JSON。

CREATE TABLE t1 (jdoc JSON);

-- 插入的数据不可以直接存储json数据格式的，要以字符串的形式存进去。
INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
INSERT INTO t1 VALUES('[1, 2]');

-- JSON_TYPE()里面放置的是一个json数据格式的字符串，用于判断一个字符串是否可以转化为json数据格式存进去，如果可以，就返回对应的json格式的数据类型
SELECT JSON_TYPE('["a", "b", 1]');   -- ARRAY

-- JSON_ARRAY()可以将值转换为数组的形式输出，其中值可以是空。
SELECT JSON_ARRAY("name","age",NOW());   -- ["name", "age", "2021-11-05 22:23:20.000000"]
SELECT JSON_ARRAY();    -- []

-- JSON_OBJECT()，将值转换为json数据格式中的对象，其中值可以是空。
SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');   -- {"key1": 1, "key2": "abc"}
SELECT JSON_OBJECT();   -- {}

-- JSON_MERGE()。拼接不同类型的值
SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');  -- ["a", 1, {"key": "value"}]

-- 创建一张新的表
CREATE TABLE facts (sentence JSON);

-- 插入一个句子
SELECT JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\".");
-- 将其作为 JSON 对象插入facts表中的一种方法 是使用 MySQL JSON_OBJECT()函数。在这种情况下，您必须使用反斜杠对每个引号字符进行转义，如下所示：
INSERT INTO facts VALUES (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));
-- 如果您将值作为 JSON 对象文字插入，则这不会以相同的方式工作，在这种情况下，您必须使用双反斜杠转义序列，如下所示：
INSERT INTO facts VALUES ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');

-- 根据键查询值
SELECT * FROM facts WHERE sentence（列名）->"$.mascot（键名）";

-- 根据键提取值
SELECT * FROM t1 WHERE JSON_EXTRACT(jdoc,'$.key3') = 'aaaaaa';

-- JSON_SET() 替换存在路径的值并为不存在的路径添加值：
SET @j = '["a", {"b": [true, false]}, [10, 20]]';
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]]      |
+--------------------------------------------+
-- $[]表示数组的第几个元素，b[]表示数组b的第几个元素。

-- JSON_INSERT() 只是添加新的值但不会替换现有值：
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() 替换现有值并忽略新值(即原来不存在的值)：
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 文档和一个或多个指定要从文档中删除的值的路径。返回值是原始文档减去文档中存在的路径选择的值：
 SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
+---------------------------------------------------+
| JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]') |
+---------------------------------------------------+
| ["a", {"b": [true]}]                              |
+---------------------------------------------------+