5mins 上手 MySQL JSON 操作

创建json

• JSON_OBJECT

# JSON_OBJECT([key, val[, key, val] ...])
select JSON_OBJECT("name","Tom","age",2)
{"age": 2, "name": "Tom"}

• JSON_ARRAY

#JSON_ARRAY([val[, val] ...])
select JSON_ARRAY(1,2,3)
[1, 2, 3]

提取json

• JSON_CONTAINS

# JSON_CONTAINS(json_doc, val[, path])
# 查询json中是否在指定path包含指定的数据，包含则返回1，否则返回0。
# 如果有参数为NULL或path不存在，则返回NULL。
SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '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('{"a": 1, "b": 2, "c": {"d": 4}}', 'one', '$.a', '$.e');
# 1
SELECT JSON_CONTAINS_PATH('{"a": 1, "b": 2, "c": {"d": 4}}', 'all', '$.a', '$.c.d');
# 1

• JSON_EXTRACT 提取json值

# JSON_EXTRACT(json_doc, path[, path] ...)
# 从json文档里抽取数据。如果有参数有NULL或path不存在，则返回NULL。
# 如果抽取出多个path，则返回的数据封闭在一个json array里。
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]
select JSON_EXTRACT('{\"id\":1,\"name\":\"yz\"}',"$.name");
# "yz"

• JSON_KEYS

# JSON_KEYS(json_doc[, path])  查找所有指定键值,为json数组
# 获取json文档在指定路径下的所有键值，返回一个json array。如果有参数为NULL或path不存在，则返回NULL。
SELECT JSON_KEYS('{"age": 2,"name": "Tom"}')
#["age","name"]
SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}'); 
# ["a", "b"]
SELECT JSON_KEYS('{"a": 1, "b": {"c": 30}}', '$.b'); 
# ["c"]

• 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

• 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_QUOTE

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

• JSON_UNQUOTE

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

返回json属性

• 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"');
# 0 1