MySQL JSON数据类型全解析（JSON datatype and functions）

JSON（JavaScript Object Notation）是一种常见的信息交换格式，其简单易读且非常适合程序处理。MySQL从5.7版本开始支持JSON数据类型，本文对MySQL中JSON数据类型的使用进行一个总结。

目录

一、MySQL中的JSON

1.1 JSON数据格式

1.2 创建JSON格式数据

1.2.1 JSON类型列

1.2.2 json_array/json_object/cast函数

二、JSON数据操作

2.1 JSON数据查询

2.1.1 json_extract()函数

2.1.2 column->path写法

2.1.3 json_value()函数

2.1.4 json_keys()函数

2.2 JSON数据新增

2.2.1 json_array_append()函数

2.2.2 json_array_insert()函数

2.3 JSON数据更新

2.3.1 json_replace()函数

2.4 JSON数据同时新增和更新

2.4.1 json_set()函数

2.5 JSON数据删除

2.5.1 json_remove()函数

2.6 JSON数据合并

2.6.1 json_merge_preserve()函数

2.6.2 json_merge_patch()函数

2.7 判断元素否存在

2.7.1 json_contains_path()函数

2.7.2 json_search()函数

2.7.3 value member of(json_array)

2.8 判断JSON对象之间的关系

2.8.1 json_contains()函数

2.8.2 json_overlaps()函数

三、JSON数据与索引

3.1 生成列索引（Generated Column Index）

3.2 多值索引（Multi-valued Index）

四、将JSON转换为结构化数据

4.1 json_table()函数

---

一、MySQL中的****JSON

在MySQL中使用JSON类型的数据有2个好处：

• 自动数据格式校验，无效的JSON格式会报错
• 数据快速解析，MySQL提供了多种函数，可以快速读取和操作JSON格式的数据

1.1 JSON数据格式

在MySQL中，JSON数据是以字符串形式表现的，但是它有自己的解析规则，利用某些途径可以将字符串转换为JSON类型数据，这个过程叫做规范化(normalization)，在规范化过程中，MySQL会对数据格式进行验证，若字符串不是有效的JSON格式，那么就会报错。

规范化除了数据格式验证，还会对数据进行一些预处理，例如JOSN对象的键值去重，排序，将boolean类型转换为小写等，规范化后的数据即被视为JSON格式数据。

JSON数据的有效格式有两种：

• JSON数组，包含在[]中的一系列值，例如: [1, 2, ‘a’, ‘b’]
• JSON对象，包含在{}中的键值对，键和值之间用冒号分隔（键必须是字符串），键值对之间以逗号分隔，如: {‘key1’:‘value1’, ‘key2’:‘value2’}

在JSON对象中，键是不能重复的，如果出现相同的键，规范化时后值会覆盖前值（MySQL 8.0.3之前保留先出现的值）。

两种JSON也格式可以互相嵌套：

• [1, 2, ‘a’, {‘key1’:‘value1’, ‘key2’:‘value2’}] --> JSON数组中某个元素是JSON对象
• {‘key1’:‘value1’, ‘key2’:[1, 2, ‘a’, ‘b’]} --> JSON对象中某个值是JSON数组

1.2 创建JSON格式数据

MySQL中主要有下列几种方式对值进行规范化，并转换成JSON格式：

• 将数据插入json类型的列中
• json_array() 函数将一组值转换为json数组
• json_object() 函数将一组键值对转换为json对象
• cast(value as json) 会根据提供的字符串格式转换为JSON数组或JSON对象

1.2.1 JSON****类型列

将表中列的类型定义为json，则所有插入的数据都会被规范化为JSON格式数据，这是最常用的创建JSON类型数据方式。

create table js(
id int primary key,
val json);

insert into js values(1, '[1,2,"a","b"]');
insert into js values(2, '{"key1":1, "key2":2, "key1":"value3"}');

select * from js;

• create table语句中，指定列的类型为json，表示其只能接受JSON格式的数据
• 两条insert 语句向JSON类型的列插入字符串时，会对字符串进行规范化并转换成JSON格式数据
• 数据插入过程中，JSON对象中重复的键key1保留了后出现的值"value3"

如果插入的字符串不是合法的JSON格式，语句会报错：

insert into js values(3, '[1,2,"a","b"');

• 这里少了一个]，语句报错：Invalid JSON text

1.2.2 json_array/json_object/cast****函数

通过 json_array() 函数可以将一系列值转换为JSON数组：

select json_array(TRUE, FALSE, NULL, 'TRUE', 'FALSE', 'NULL');

• 这里TRUE, FALSE, NULL在规范化过程中均被转换为小写
• 而字符串’TRUE’, ‘FALSE’, 'NULL’则保持不变

通过 json_object() 函数可以把字符串形式的键值对转换成JSON格式对象：

select json_object('key2',2, 'key1',1, 'key1',3);

• 返回的结果中，key1后出现的值3覆盖了先出现的值1
• 因为要凑键值对，所以提供的值的数量必须是偶数
• 规范化后，JSON对象是按键排序的，本例中key1被排到的前面

由于键必须是字符串，即使以数字形式提供，在规范化的过程中也会被转换为字符串：

select json_object('key2',2, 'key1',1, 3, 'key1');

• 第三个键值对以数字3作为键，规范化后，3被转换成字符串"3"并被排到了最前面

同样，cast(… as json)会根据提供的字符串格式，转换为JSON数组或JSON对象：

select cast('[1,2,"abc"]' as json);??? -- 转换为JSON数组

select cast('{"key1":1, "key2":2, "key1":3}' as json);??? -- 转换为JSON对象

二、JSON数据操作

使用JSON格式的最大好处是MySQL提供了一系列操作函数，可以快速对JSON类型的数据进行查询/更新，不必再对整个字符串进行格式解析，在处理JSON数据时简便许多。

2.1 JSON****数据查询

数据被转换为JSON格式后，即可对其进行快速解析，高效提取数据。

2.1.1 **json_extract()**函数

json_extract()函数可以从JSON格式的数据中快速取出指定值，语法如下：

• json_extract(js_array, 'KaTeX parse error: Undefined control sequence: \[ at position 1: \̲[̲n\]') 通过'[n]'的形式取出JSON数组中编号为n的元素（编号从0开始）
• json_extract(js_array, 'KaTeX parse error: Undefined control sequence: \[ at position 1: \̲[̲m to n\]') 通过'[m to n]'的形式取出JSON数组中编号m到n的所