MySQL 5.7 JSON相关操作

亲身试验过：

官网IP：https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html

create table demo(id int unsigned primary key auto_increment,comment json);

insert into demo(id,name) values(1,"{"programmers": [{"email": "aaaa", "lastName": "McLaughlin", "firstName": "Brett"}, {"email": "bbbb", "lastName": "Hunter", "firstName": "Jason"}]}");

-- 检查第一层的key值 json_keys

-- select json_keys(comment) from demo


-- 从JSON中提取 json_extract
-- select json_extract(comment,"$.programmers[0].email") from demo 


-- 从Json中去除元素 json_remove
-- select json_extract(comment,"$.programmers"),json_remove(comment,"$.programmers[0]") from demo


-- Json是否包含当前路径 json_contains_path
-- select json_contains_path(comment,"all","$.programmers[0].firstName") from demo 


-- 判断JSON当前路径对象类型 JSON_TYPE
-- select JSON_TYPE(comment),JSON_TYPE(comment->"$.programmers[0].firstName"),JSON_TYPE(comment->"$.programmers")="ARRAY" from demo


-- 创建数组对象 JSON_ARRAY
-- SELECT JSON_ARRAY("a", 1, RAND());


-- 将对象列表转化成JSON对象(key唯一,必须是偶数 key - value key - value) JSON_OBJECT
-- SELECT JSON_OBJECT("key1", 1, "key2", "abc"),JSON_OBJECT("key1", 1, "key2", "abc", "key1", "def"); 


-- 将两个或以上的JSON对象融合 JSON_MERGE
-- SELECT JSON_MERGE("["a", 1]", "{"key": "value"}"),JSON_MERGE("{"a": 2,"b":"2"}","{"key": "value"}"),JSON_MERGE("{"a": 1, "b": 2}", "{"c": 3, "a": 4}");


-- 用户定义的变量不能JSON数据类型 函数 COLLATION(返回字符串参数的排序方式)
-- SET @j = JSON_OBJECT("key", "value"); SELECT CHARSET(@j), COLLATION(@j);


-- 因为utf8mb4_bin是二进制排序规则，JSON值比较是区分大小写的。
-- SELECT JSON_ARRAY("x") = JSON_ARRAY("X");


-- JSON对大小写敏感,SQL非空不敏感
-- SELECT JSON_VALID("null"), JSON_VALID("Null"), JSON_VALID("NULL"),ISNULL(null), ISNULL(Null), ISNULL(NULL);


-- 将JSON中元素替换;如果位置不存在，则追加 JSON_SET
-- SET @j = "["a", {"b": [true, false]}, [10, 20]]";SELECT JSON_SET(@j, "$[1].b[0]", 1, "$[2][3]", 3);


-- 向JSON中添加元素,原来位置存在数据不会替换 JSON_INSERT
-- SELECT JSON_INSERT(@j, "$[1].b[0]", 1, "$[2][2]", 2);


-- 替换JSON中原有值,不存在的不会替换 JSON_REPLACE
-- SELECT JSON_REPLACE(@j, "$[1].b[0]", JSON_OBJECT("key", "value"), "$[2][2]", 2);


-- 移除JSON元素列表 JSON_REMOVE
-- SELECT JSON_REMOVE(@j, "$[2]", "$[1].b[1]", "$[1].b[1]");


-- 判断JSON中是否包含"值",在这个路径下 JSON_CONTAINS
-- SELECT JSON_CONTAINS("{"a": 1, "b": 2, "c": {"d": 4}}", "1", "$.a");


-- 去掉JSON格式""号 json_unquote , ->> 这两个是等价的
-- select json_unquote(comment->"$.programmers[1].email"), comment->>"$.programmers[1].email"from demo


-- 5.7mysql创建函数索引(类似于触发器)
-- ALTER TABLE demo ADD COLUMN index_for_Json VARCHAR(128) AS (comment->>"$.programmers[1].email") VIRTUAL;
-- ALTER TABLE demo ADD INDEX index_Json (index_for_Json);


-- JSON格式查询value :JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...]) 第三个参数可以是like表达式 第四个参数 是起始搜索位置,第5个参数是 终止收索位置
-- SET @j = "["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]";SELECT JSON_SEARCH(@j, "one", "abc"),JSON_SEARCH(@j, "all", "abc"),JSON_SEARCH(@j, "all", "%b%", "", "$[1]"),JSON_SEARCH(@j, "all", "%b%", "", "$[3]");