在mysql5.7之前我们查询解析json需要先查出json字符串,再通过程序进行解析。(如果你使用的navicat版本太低的话,看不到json类型的)

现在mysql5.7引入了json类型格式,大大方便了我们的查询解析。

MySQL官方列出json相关的函数,完整列表如下:

分类 函数 描述
创建json json_array 创建json数组
json_object 创建json对象
json_quote 将json转成json字符串类型
查询json json_contains 判断是否包含某个json值
json_contains_path 判断某个路径下是否包json值
json_extract 提取json值
column->path json_extract的简洁写法,MySQL 5.7.9开始支持
column->>path json_unquote(column -> path)的简洁写法
json_keys 提取json中的键值为json数组
json_search 按给定字符串关键字搜索json,返回匹配的路径
修改json json_append 废弃,MySQL 5.7.9开始改名为json_array_append
json_array_append 末尾添加数组元素,如果原有值是数值或json对象,则转成数组后,再添加元素
json_array_insert 插入数组元素
json_insert 插入值(插入新值,但不替换已经存在的旧值)
json_merge 合并json数组或对象
json_remove 删除json数据
json_replace 替换值(只替换已经存在的旧值)
json_set 设置值(替换旧值,并插入不存在的新值)
json_unquote 去除json字符串的引号,将值转成string类型
返回json属性 json_depth 返回json文档的最大深度
json_length 返回json文档的长度
json_type 返回json值得类型
json_valid 判断是否为合法json文档

例子

建表的时候选择字段类型为JSON类型

1.png

插入测试数据

2.png

json所在字段的数据格式

5.png

查询测试数据
SELECT id,uniqueid,`name`, configs->'$[3].value' as seotitle from  active_pages;

4.png

json_array 生成json数组

-- JSON_ARRAY(val1,val2,val3...)
-- 生成一个包含指定元素的json数组。
SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()); -- [1, "abc", null, true, "10:37:08.000000"]

json_object 生成json对象

-- JSON_OBJECT(key1,val1,key2,val2...)
-- 生成一个包含指定K-V对的json object。如果有key为NULL或参数个数为奇数,则抛错。
SELECT JSON_OBJECT('age', 20, 'time', now()); -- {"age": 20, "time": "2021-06-18 14:49:57.000000"}

json_quote 加"号

-- JSON_QUOTE(json_val)
-- 将json_val用"号括起来。
SELECT JSON_QUOTE('[1,2,3]'); -- "[1,2,3]" 

json_contains 指定数据是否存在

set @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
-- JSON_CONTAINS(json_doc, val[, path])
-- 查询json文档是否在指定path包含指定的数据,包含则返回1,否则返回0。如果有参数为NULL或path不存在,则返回NULL。
SELECT JSON_CONTAINS(@j, '4', '$.c.d'); -- 1

json_contains_path 指定路径是否存在

-- JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
-- 查询是否存在指定路径,存在则返回1,否则返回0。如果有参数为NULL,则返回NULL。
-- one_or_all只能取值"one"或"all",one表示只要有一个存在即可;all表示所有的都存在才行。
SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e'); -- 1
SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.c.d'); -- 1

json_extract 查找所有指定数据

-- JSON_EXTRACT(json_doc, path[, path] ...)
-- 从json文档里抽取数据。如果有参数有NULL或path不存在,则返回NULL。如果抽取出多个path,则返回的数据封闭在一个json array里。
set @j2 = '[10, 20, [30, 40]]';
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]'); -- 20
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]'); -- [20, 10]
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]'); -- [30, 40]

json_keys 查找所有指定键值

-- JSON_KEYS(json_doc[, path])
-- 获取json文档在指定路径下的所有键值,返回一个json array。如果有参数为NULL或path不存在,则返回NULL。
SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}'); -- ["a", "b"]
SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b'); -- ["c"]
SELECT id,json_keys(info) FROM t_json;

json_search 查找所有指定值的位置

-- JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])
-- 查询包含指定字符串的paths,并作为一个json array返回。如果有参数为NUL或path不存在,则返回NULL。
-- one_or_all:"one"表示查询到一个即返回;"all"表示查询所有。
-- search_str:要查询的字符串。 可以用LIKE里的'%'或‘_’匹配。
-- path:在指定path下查。
SET @j3 = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
SELECT JSON_SEARCH(@j3, 'one', 'abc'); -- "$[0]"
SELECT JSON_SEARCH(@j3, 'all', 'abc'); -- ["$[0]", "$[2].x"]
SELECT JSON_SEARCH(@j3, 'all', 'abc', NULL, '$[2]'); -- "$[2].x"
SELECT JSON_SEARCH(@j3, 'all', '10'); -- "$[1][0].k"
SELECT JSON_SEARCH(@j3, 'all', '%b%'); -- ["$[0]", "$[2].x", "$[3].y"]
SELECT JSON_SEARCH(@j3, 'all', '%b%', NULL, '$[2]'); -- "$[2].x"

json_array_append 指定位置追加数组元素

-- JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
-- 在指定path的json array尾部追加val。如果指定path是一个json object,则将其封装成一个json array再追加。如果有参数为NULL,则返回NULL。
SET @j4 = '["a", ["b", "c"], "d"]';
-- SELECT JSON_ARRAY_APPEND(@j4, '$[1][0]', 3); -- ["a", [["b", 3], "c"], "d"]
SET @j5 = '{"a": 1, "b": [2, 3], "c": 4}';
SELECT JSON_ARRAY_APPEND(@j5, '$.b', 'x'); -- {"a": 1, "b": [2, 3, "x"], "c": 4} 
SELECT JSON_ARRAY_APPEND(@j5, '$.c', 'y'); -- {"a": 1, "b": [2, 3], "c": [4, "y"]}
SELECT JSON_ARRAY_APPEND(@j5, '$', 'z'); -- [{"a": 1, "b": [2, 3], "c": 4}, "z"]

json_array_insert 指定位置插入数组元素

-- JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
-- 在path指定的json array元素插入val,原位置及以右的元素顺次右移。如果path指定的数据非json array元素,则略过此val;如果指定的元素下标超过json array的长度,则插入尾部。
SET @j6 = '["a", {"b": [1, 2]}, [3, 4]]';
SELECT JSON_ARRAY_INSERT(@j6, '$[1]', 'x'); -- ["a", "x", {"b": [1, 2]}, [3, 4]]
SELECT JSON_ARRAY_INSERT(@j6, '$[100]', 'x'); -- ["a", {"b": [1, 2]}, [3, 4], "x"]
SELECT JSON_ARRAY_INSERT(@j6, '$[1].b[0]', 'x'); -- ["a", {"b": ["x", 1, 2]}, [3, 4]]
SELECT JSON_ARRAY_INSERT(@j6, '$[0]', 'x', '$[3][1]', 'y'); -- ["x", "a", {"b": [1, 2]}, [3, "y", 4]]

json_insert 指定位置插入

-- JSON_INSERT(json_doc, path, val[, path, val] ...)
-- 在指定path下插入数据,如果path已存在,则忽略此val(不存在才插入)。
SET @j7 = '{ "a": 1, "b": [2, 3]}';
SELECT JSON_INSERT(@j7, '$.a', 10, '$.c', '[true, false]'); -- {"a": 1, "b": [2, 3], "c": "[true, false]"}

json_replace 指定位置替换

-- JSON_REPLACE(json_doc, path, val[, path, val] ...)
-- 替换指定路径的数据,如果某个路径不存在则略过(存在才替换)。如果有参数为NULL,则返回NULL。
SELECT JSON_REPLACE(@j7, '$.a', 10, '$.c', '[true, false]'); -- {"a": 10, "b": [2, 3]}

json_set 指定位置设置

-- JSON_SET(json_doc, path, val[, path, val] ...)
-- 设置指定路径的数据(不管是否存在)。如果有参数为NULL,则返回NULL。
SELECT JSON_SET(@j7, '$.a', 10, '$.c', '[true, false]'); -- {"a": 10, "b": [2, 3], "c": "[true, false]"}

json_merge 合并

-- JSON_MERGE(json_doc, json_doc[, json_doc] ...)
-- merge多个json文档。规则如下:
-- 如果都是json array,则结果自动merge为一个json array;
-- 如果都是json object,则结果自动merge为一个json object;
-- 如果有多种类型,则将非json array的元素封装成json array再按照规则一进行mege。
SELECT JSON_MERGE('[1, 2]', '[true, false]'); -- [1, 2, true, false]
SELECT JSON_MERGE('{"name": "x"}', '{"id": 47}'); -- {"id": 47, "name": "x"}
SELECT JSON_MERGE('1', 'true'); -- [1, true]
SELECT JSON_MERGE('[1, 2]', '{"id": 47}'); -- [1, 2, {"id": 47}]

json_remove 指定位置移除

-- JSON_REMOVE(json_doc, path[, path] ...)
-- 移除指定路径的数据,如果某个路径不存在则略过此路径。如果有参数为NULL,则返回NULL。
SET @j8 = '["a", ["b", "c"], "d"]';
SELECT JSON_REMOVE(@j8, '$[1]'); -- ["a", "d"]

json_unquote 去"号

-- JSON_UNQUOTE(val)
-- 去掉val的引号。如果val为NULL,则返回NULL。
SELECT JSON_UNQUOTE("\"123\""); -- 123

json_depth 深度

-- JSON_DEPTH(json_doc)
-- 获取json文档的深度。如果参数为NULL,则返回NULL。
-- 空的json array、json object或标量的深度为1。
SELECT JSON_DEPTH('{}'), JSON_DEPTH('[]'), JSON_DEPTH('true'); -- 1 1 1
SELECT JSON_DEPTH('[10, 20]'), JSON_DEPTH('[[], {}]'); -- 2 2
SELECT JSON_DEPTH('[10, {"a": 20}]'); -- 3

json_length 长度

-- JSON_LENGTH(json_doc[, path])
-- 获取指定路径下的长度。如果参数为NULL,则返回NULL。 
-- 长度的计算规则:
-- 标量的长度为1;
-- json array的长度为元素的个数;
-- json object的长度为key的个数。
SELECT JSON_LENGTH('[1, 2, {"a": 3}]'); -- 3
SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}'); -- 2
SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b'); -- 1

json_type 类型

-- JSON_TYPE(json_val)
-- 获取json文档的具体类型。如果参数为NULL,则返回NULL。
select JSON_TYPE('[1,2]'); -- ARRAY

json_valid 是否有效json格式

-- JSON_VALID(val)
-- 判断val是否为有效的json格式,是为1,不是为0。如果参数为NUL,则返回NULL。
SELECT JSON_VALID('{"a": 1}'); -- 1
SELECT JSON_VALID('hello'), JSON_VALID('"hello"'); -- 1

总结

    json_array 生成json数组
    json_object 生成json对象
    json_quote 加"号
    json_contains 指定数据是否存在
    json_contains_path 指定路径是否存在
    json_extract 查找所有指定数据
    json_keys 查找所有指定键值
    json_search 查找所有指定值的位置
    json_array_append  指定位置追加数组元素
    json_array_insert 指定位置插入数组元素
    json_insert 指定位置插入
    json_replace 指定位置替换
    json_set 指定位置设置
    json_merge 合并
    json_remove 指定位置移除
    json_unquote 去"号
    json_depth 深度
    json_length 长度
    json_type 类型
    json_valid 是否有效json格式

关注友儿不迷路

Last modification:October 14th, 2021 at 05:15 pm
如果觉得我的文章对你有用,请随意赞赏