MySQL Json操作总结 - 数据库

MySQL 5.7.8 新增了对 Json 数据的相关支持，可以对 JsonObject 数据进行解析、查询等操作。

MySQL 8.0.4 新增了 Json 表函数的功能，可以将 JsonArray 数据解析为表格形式，再进行查询等操作。

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_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_OBJECT

SELECT JSON_OBJECT('key1', 'aaa', 'key2', 'bbb', 'key1', 'ccc');

>> {"key1": "aaa", "key2": "bbb"}

JSON_ARRAY

创建 Json Array

SELECT JSON_ARRAY('aaa',true,'bbb',10);

>> [aaa, true ,"bbb", 10]

JSON_QUOTE

通过用双引号字符包裹字符串并转义内部引号和其他字符，将 utf8mb4 字符串引用为 Json 值，然后将结果作为字符串返回。

原始 json_data 列数据：
>> {"a": 1, "b": {"c": 30}}

SELECT JSON_QUOTE(json_data) FROM json_demo;
>> "{"a": 1, "b": {"c": 30}}"

查找 JSON

JSON_EXTRACT

SELECT JSON_EXTRACT(json_data, '$.version') FROM json_demo;

>> "My MySQL version is "5.7.33"."

JSON_UNQUOTE

如果想不包括周围的引号或任何转义符，请使用内联路径运算符 ->> ，如下所示：

SELECT json_data,json_data->>'$.version' FROM json_demo;

>> My MySQL version is "5.7.33".

或者使用 JSON_UNQUOTE 函数，可以去除 Json 字符串的引号，将值转成 String 类型：

SELECT JSON_UNQUOTE(JSON_EXTRACT(json_data, '$.version')) FROM json_demo;

>> My MySQL version is "5.7.33".

JSON_CONTAINS

格式如：JSON_CONTAINS(target, candidate[, path])

通过返回 1 或 0 指示给定的 candidate Json 文档是否包含在 target Json 文档中。

如果提供了 *path* 参数，则在目标内的特定路径中查找候选者。

原始 json_data 列数据：
>> {"a": 1, "b": {"c": 30}}

查询文档 "a":1 是否在上述 Json 中
SELECT JSON_CONTAINS(json_data,JSON_OBJECT('a',1)) FROM json_demo;
>> 1

查询 30 是否在指定的 JsonPath 中
SELECT JSON_CONTAINS(json_data,'30','$.b.c') FROM json_demo;
>> 1

JSON_CONTAINS_PATH

格式如：JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

返回 0 或 1 以指示 Json 文档是否包含给定路径或多个路径中的数据。

如果文档中不存在指定路径，则返回值为 0。否则，返回值取决于 *one_or_all* 参数：

'one': 如果文档中至少存在一个路径，则为 1，否则为 0。
'all': 1 如果文档中存在所有路径，否则为 0。

原始 json_data 列数据：
>> {"a": 1, "b": {"c": 30}}

查询 one ，至少存在一个
SELECT JSON_CONTAINS_PATH(json_data,'one','$.a','$.b','$.c') FROM json_demo;
>> 1

查询 all , 存在所有路径
SELECT JSON_CONTAINS_PATH(json_data,'all','$.a','$.b','$.c') FROM json_demo;
>> 0

JSON_KEYS

将 Json 对象的顶级值中的键作为 Json 数组返回，或者，如果 path 给出了参数，则返回所选路径中的顶级键。

原始 json_data 列数据：
>> {"a": 1, "b": {"c": 30}}

SELECT JSON_KEYS(json_data) FROM json_demo;
>> ["a", "b"]

SELECT JSON_KEYS(json_data,'$.b') FROM json_demo;
>> ["c"]

JSON_SEARCH

格式如：JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

返回 Json 文档中给定字符串的路径。

该 *one_or_all* 参数影响搜索如下：

'one'：搜索在第一次匹配后终止并返回一个路径字符串。未定义首先考虑哪个匹配。
'all'：搜索返回所有匹配的路径字符串，这样就不会包含重复的路径。如果有多个字符串，它们会自动包装为一个数组。数组元素的顺序未定义。

原始 json_data 列数据：
>> ["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]

one 第一次匹配
SELECT JSON_SEARCH(json_data,'one','abc') FROM json_demo;
>> "$[0]"

all 所有匹配
SELECT JSON_SEARCH(json_data,'all','abc') FROM json_demo;
>> ["$[0]", "$[2].x"]

SQL 运用

通过 json_data->'$.version' 或 json_data->>'$.version' 提取出来的 Json 字段，可以直接运用在 SQL 语句中，比如进行条件查询或者 order 排序等操作。

例如：

原始 json_data 列数据：
>> {"name": "wyq", "age": 29}

= 查询：
SELECT * FROM json_demo where json_data->>'$.name' ='wyq';

like 查询：
SELECT * FROM json_demo where json_data->>'$.name' like '%wyq%';

大小于查询：
SELECT * FROM json_demo where json_data->>'$.age' < 35;

排序：
SELECT * FROM json_demo order by json_data->>'$.age' desc;

修改 JSON

JSON_MERGE：合并

SELECT JSON_MERGE('{"key1": 1, "key2": 2}', '{"key3": 3, "key1": 4}');

>> {"key1": [1, 4], "key2": 2, "key3": 3}

将对象自动包装为数组、合并两个数组和对象值：

SELECT JSON_MERGE('[10, 20]', '{"a": "x", "b": "y"}');

>> [10, 20, {"a": "x", "b": "y"}]

JSON_SET：替换

JSON_SET () 可以替换存在路径的值、或者为不存在的路径添加值：

原始 json_data 列数据：
>> {"version": "My MySQL version is "5.7.33"."}

修改 value 值：
SELECT JSON_SET(json_data, '$.version', 'new version info') FROM json_demo;
>> {"version": "new version info"}

添加值：
SELECT JSON_SET(json_data, '$[1]', JSON_OBJECT("newkey","new info")) FROM json_demo;
>> [{"version": "My MySQL version is "5.7.33"."}, {"newkey": "new info"}]

JSON_INSERT：添加

JSON_INSERT () 添加新值但不替换现有值。

JSON_REPLACE：替换

JSON_REPLACE () 替换现有值并忽略新值。

JSON_REMOVE：删除

JSON_REMOVE () 接受一个 Json 文档和一个或多个指定要从文档中删除的值的路径。

返回值是原始文档减去文档中存在的路径选择的值：

原始 json_data 列数据：
>> {"version": "My MySQL version is "5.7.33"."}

SELECT JSON_REMOVE(json_data, '$.version') FROM json_demo;
>> {}

原始 json_data 列数据：
>> ["a", {"b": [1, false]}, [10, 20]]

SELECT JSON_REMOVE(json_data, '$[2]', '$[1].b[1]', '$[1].b[1]') FROM json_demo;
>> ["a", {"b": [1]}]

Json 属性

JSON_DEPTH：深度

返回 Json 文档的最大深度。

空数组、空对象或标量值的深度为 1。仅包含深度为 1 的元素的非空数组或仅包含深度为 1 的成员值的非空对象的深度为 2。

原始 json_data 列数据：
>> {"a": 1, "b": {"c": 30}}

SELECT JSON_DEPTH(json_data) FROM json_demo;
>> 3

原始 json_data 列数据：
>> ["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]

SELECT JSON_DEPTH(json_data) FROM json_demo;
>> 4

JSON_LENGTH：长度

返回 Json 文档的长度。

文档的长度确定如下：

标量的长度为 1。
数组的长度是数组元素的数量。
对象的长度是对象成员的数量。
长度不计算嵌套数组或对象的长度。

原始 json_data 列数据：
>> {"a": 1, "b": {"c": 30}}

SELECT JSON_LENGTH(json_data) FROM json_demo;
>> 2

原始 json_data 列数据：
>> ["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]

SELECT JSON_LENGTH(json_data) FROM json_demo;
>> 4

JSON_TYPE：类型

返回 utf8mb4 指示 Json 值类型的字符串。这可以是 OBJECT、ARRAY、INTEGER、BOOLEAN。

原始 json_data 列数据：
>> ["a", {"b": [1, false]}, [10, 20]]

SELECT JSON_TYPE(json_data) FROM json_demo;
>> ARRAY            

SELECT JSON_TYPE(JSON_EXTRACT(json_data, '$[1]')) FROM json_demo;
>> OBJECT

SELECT JSON_TYPE(JSON_EXTRACT(json_data, '$[1].b[1]')) FROM json_demo;
>> BOOLEAN

JSON_VALID：有效

返回 0 或 1 以指示值是否为有效的 JSON。

原始 json_data 列数据：
>> ["a", {"b": [1, false]}, [10, 20]]

SELECT JSON_VALID(json_data) FROM json_demo;
>> 1

原始 json_data 列数据：
>> hello

SELECT JSON_VALID(json_data) FROM json_demo;
>> 0

原始 json_data 列数据：
>> "hello"

SELECT JSON_VALID(json_data) FROM json_demo;
>> 1

JSON_TABLE 表操作

在 MySQL 8.0.4 及更高版本中，JSON_TABLE 函数可以将 Json 数据转换为表格数据的 Json 函数的信息。

格式为：

JSON_TABLE(
    expr,
    path COLUMNS (column_list)
)   [AS] alias

注意：

必须起别名 alias，否则会报错 Every table function must have an alias。

示例：

set @jsoninfo='[{"name":"Alice"},{"name":"Bob"},{"name":"Cindy"}]';
SELECT * from JSON_TABLE(@jsoninfo,
                         "$[*]" COLUMNS( 
                             rowid FOR ORDINALITY,
                             name VARCHAR(100) PATH "$.name")) as t;
查询结果：
+-------+-------+
| rowid | name  |
+-------+-------+
|     1 | Alice |
|     2 | Bob   |
|     3 | Cindy |
+-------+-------+

从数据库中进行取值：

原始 json_data 列数据：
>> [{"name":"Alice"},{"name":"Bob"},{"name":"Cindy"}]

SELECT t.* from json_demo,JSON_TABLE(json_data,"$[*]" COLUMNS( rowid FOR ORDINALITY,name VARCHAR(100) PATH "$.name")) as t;

查询结果：
+-------+-------+
| rowid | name  |
+-------+-------+
|     1 | Alice |
|     2 | Bob   |
|     3 | Cindy |
+-------+-------+

原始 json_data 列数据（在表中 id = 2）：
>> [{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]

SELECT t.* from json_demo a,
JSON_TABLE(json_data,"$[*]" COLUMNS( 
    rowid FOR ORDINALITY,xval VARCHAR(100) PATH "$.x",
    yval VARCHAR(100) PATH "$.y")) as t
where a.id=2;

查询结果：
+-------+------+------+
| rowid | xval | yval |
+-------+------+------+
|     1 | 2    | 8    |
|     2 | 3    | 7    |
|     3 | 4    | 6    |
+-------+------+------+

Json 虚拟列

新增虚拟列

建立员工信息表如下所示，其中 JSON_EXTRACT 和 JSON_UNQUOTE 都是上面介绍过的：

CREATE TABLE `employee` (
  `id` int NOT NULL AUTO_INCREMENT,
  `json_data` mediumtext NULL,
  `name` varchar(20) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(`json_data`,'$.name'))) VIRTUAL NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
) ENGINE=InnoDB

或者使用内联路径运算符 ->> ，如下所示：

CREATE TABLE `employee` (
  `id` int NOT NULL AUTO_INCREMENT,
  `json_data` mediumtext NULL,
  `name` varchar(20) GENERATED ALWAYS AS (json_data->>'$.name') VIRTUAL NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
) ENGINE=InnoDB

其中，name 列即为虚拟字段，它是根据 json_data 列的 Json 数据，自动生成的。

对表中插入数据：

// Json 中包含 name 
INSERT INTO employee (`json_data`) VALUES ('{"name":"Alice","age":25}');
INSERT INTO employee (`json_data`) VALUES ('{"name":"Bob","age":28}');
INSERT INTO employee (`json_data`) VALUES ('{"name":"Cindy","age":24}');

// Json 中不包含 name 
INSERT INTO employee (`json_data`) VALUES ('{"sex":"male","age":24}');

// 插入非 Json 的数据
INSERT INTO employee (`json_data`) VALUES ('test text info');

可以发现，前面四条数据都添加成功了，第四条由于不包含 name ，所以 name 虚拟列为 null。

但五条数据报错，因为它不是 Json 格式：

Invalid Json text in argument 1 to function json_extract: "Invalid value." at position 1

使用 JSON_SET 更新某条 Json 数据，可以发现虚拟列也同步进行了更新：

原始 json_data 列数据：
>> {"name":"Bob","age":28}

update employee set json_data=JSON_SET(json_data, '$.name', 'Bassam') where id=2;

更新后数据：
+----+-------------------------------+--------+
| id | json_data                     | name   |
+----+-------------------------------+--------+
|  2 | {"age": 28, "name": "Bassam"} | Bassam |
+----+-------------------------------+--------+

虚拟列索引

分析查询语句可以发现：

explain SELECT name FROM employee where name='Bob';

会用到 name 虚拟列的 name_index 索引。

通过 json_data->'$.name' 查询，也会用到虚拟列的 name_index 索引。

explain SELECT name FROM employee where json_data->'$.name'='Bob';

省略虚拟列

建立索引的时候，也可以省略虚拟列，直接创建索引。

建表示例如下：

CREATE TABLE `employee` (
  `id` int NOT NULL AUTO_INCREMENT,
  `json_data` mediumtext,
  PRIMARY KEY (`id`),
  KEY `json_data_name_index` ((cast(`json_data`->>'$.name' as char(255))))
) ENGINE=InnoDB

新增索引格式：

ALTER TABLE table_name ADD KEY ( (CAST(column_name -> '$' AS UNSIGNED ARRAY)) );

新增语句如前所述。

查看 explain 执行计划：

explain SELECT * FROM `test`.`employee` where json_data->>'$.name'='Bob';

可以发现并没有用到所创建的索引。

这是由于前面表定义中的索引表达式 WHERE 与查询中的子句表达式之间存在排序规则不匹配，参考。

可以在查询中指定完整表达式：

explain SELECT * FROM `test`.`employee` where CAST(json_data->>'$.name'AS CHAR(25))='Bob';

即可看到，使用了所创建的索引。

explain FORMAT=TREE SELECT * FROM `test`.`employee` where CAST(json_data->>'$.name'AS CHAR(25))='Bob';

>> -> Index lookup on employee using json_data_name_index (cast(json_unquote(json_extract(json_data,_utf8mb3'$.name')) as char(25) charset utf8)='Bob')  (cost=0.35 rows=1)