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]');