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, '$[n]') 通过'$[n]'的形式取出JSON数组中编号为n的元素（编号从0开始）
- json_extract(js_array, '$[m to n]') 通过'$[m to n]'的形式取出JSON数组中编号m到n的所有元素（编号从0开始）
- json_extract(js_object, '$.key') 通过'$.key'的形式取出JSON对象中键为key所对应的值
- '$[n]'和'$.key'还可以替换为'$[*]'和'$.*'表示返回所有的值
从JSON数组取出单个值：
select * from js;
select id, json_extract(val, '$[1]') from js;
- json_extract(val, '$[1]')取出JSON数组中第二个元素
- 如果目标是JSON对象，则返回null（id为2的记录）
从JSON数组中取出范围值：
select id, json_extract(val, '$[1 to 2]') from js;
- json_extract(val, '$[1 to 2]')取出JSON数组中2~3范围内的元素
从JSON对象中取出单个值
select id, json_extract(val, '$.key1') from js;
- json_extract(val, '$.key1')取出JSON数组中键为"key1"所对应的值
2.1.2 column->path写法
json_extract函数还有一种更简便的column->path写法，用'->'或'->>'符号来替代json_extract()：
- json_extract(js_array, '$[n]') 等价于 js_array->'$[n]'
- json_extract(js_array, '$[n to m]') 等价于 js_array->'$[n to m]'
- json_extract(js_object, '$.key') 等价于 js_object->'$.key'
上面示例的查询也可以用以下的简便写法
select val->'$[1]' from js where id=1;
select val->'$[1 to 3]' from js where id=1;
select val->'$[*]' from js where id=1;
- val->'$[1]' 返回JSON数组中第2个元素
- val->'$[1 to 3]' 返回JSON数组中第2至4范围内元素
- val->'$[*]' 返回数组中所有元素
JSON对象也一样：
select val->'$.key1' from js where id=2;
select val->>'$.key1' from js where id=2;
select val->>'$.*' from js where id=2;
- 注意符号->和->>的输出结果差异，->的结果是带引号的，如果想要的是不带引号的值，使用->>即可
- ->>相当于joson_unqoute(json_extract(column, path))，即对结果又做了一次去除引号的操作。
- val->>'$.*' 返回JSON对象中所有值
2.1.3 json_value()函数
json_value()函数也可以通过path查询指定元素的值，但是它还有额外的类型转换及异常处理功能，在使用上比json_extract()稍复杂，但功能更强。
语法为：json_value(json_doc, path [returning type] [{null|error|default value} on empty] [{null|error|default value} on error])
语法看起来比较复杂，其实就是多了返回值类型转换(returning type)，空值处理(on empty)，错误处理(on error)3个模块，下面通过几个示例演示该函数用法：
首先是最简单的用法，只需要提供JSON数据及path就可以提取特定位置的值，用法和json_extract函数一样：
set @array1 = '[1, 2, "a", "b", "12.345"]';
set @object1 = '{"key1":"value1", "key2":"value2"}';
select @array1, json_value(@array1, '$[4]');
select @object1, json_value(@object1, '$.key1');
在提取数据基础上，通过新增returning type选项即可对返回值进行类型转换：
select @array1, json_value(@array1, '$[4]' returning decimal(4,2));
select @array1, json_value(@array1, '$[4]' returning char);
select @array1, json_value(@array1, '$[4]' returning json);
- 第一个select返回值的类型定义为decimal(4,2)，保理2位小数，因此12.345返回了1.35。
- 第二个select返回字符串类型，由于默认就是返回字符串类型，这个returning char可以省略
- 第三个select返回JSON类型
on empty选项定义了在找不到数据时，函数该如何处理，有3种选项：
- null on empty, 这是默认选项，找不到数据时返回null
- default value on empty, 找不到数据时返回默认值value
- error on empty, 函数抛出错误，由用户处理
select @array1, json_value(@array1, '$[100]' null on empty);
select @array1, json_value(@array1, '$[100]' default '没找到' on empty);
select @array1, json_value(@array1, '$[100]' error on empty);
- 上面3个选项分别演示了3种on empty的处理，null on empty是默认选项，可以省略
on error选项定义了当函数出错时，应该如何处理，注意这里的出错是指函数在运行过程中出错，例如类型转换错误，如果提供的JSON数据格式或者path格式错误，那么函数本身直接运行出错，不会触发on error选项，这里on error选项也有3种：
- null on error, 默认选项，错误返回null
- default value on error, 返回一个默认值，默认值必须要和returning type匹配
- error on error, 函数抛出错误，由用户处理
select @array1, json_value(@array1, '$[2]' returning decimal(4,2) null on error) convert_a_to_decimal;
select @array1, json_value(@array1, '$[2]' returning decimal(4,2) default 56.78 on error) default_value_on_error;
select @array1, json_value(@array1, '$[2]' returning decimal(4,2) error on error) error_on_error;
- 上面3个select尝试将'a'转换为decimal类型，on error选项演示了3种不同的处理
- 第二个选项defulat value，value必须和前面的returning type中的type匹配（这里是decimal(4,2)）
2.1.4 json_keys()函数
json_extract()/json_value()函数返回的都是JSON对象的值，如果想返回键，则需要用json_keys()函数。json_keys()会以JSON数组的形式返回JSON对象中顶层的键，即将所有的键组成一个数组返回。
其语法为: json_keys(json_doc [, path])，当提供path参数时（JSON对象嵌套），会返回指定path处元素的顶层键
返回JSON对象的所有顶层键：
select json_keys('{"key1": "value3", "key2": {"a":"b"}}');
- 这里的顶层键有'key1','key2'，其中'key2'的值又是一个JSON对象（嵌套）
- 没有指定path参数，这里只返回了顶层的键
如果要返回key2值中的键，需要提供path参数'$.key2'
select json_keys('{"key1": "value3", "key2": {"a":"b"}}', '$.key2');
2.2 JSON数据新增
JSON对象的中的元素新增，可以通过json_arry_append()，json_array_insert()或json_insert()函数来完成，各函数行为略有不同。
2.2.1 json_array_append()函数
josn_arry_append() 会在JSON数组指定的位置添加新的数据，新增的数据与原位置的数据会合并为新的JSON数组（依然算1个元素），不会改变原JSON数据的元素个数，函数语法为：json_arry_append(json_doc, path, val [,path, val] …)
select * from js;
update js set val=json_array_append(val,'$[0]','x', '$[3]','y') where id=1;
update js set val=json_array_append(val,'$.key2','y') where id=2;
select * from js;
- json_array_append(val,'$[0]','x', '$[3]','y') 在JSON数组第一个元素中增加一个'x'，第三个元素中增加一个'y'，数组的元素依然是4个
- json_array_append(val,'$.key2','y') 在JSON对象键key2的值中附件一个'y'
2.2.2 json_array_insert()函数
josn_arry_insert() 会在JSON数组指定的位置添加新的数据，与json_array_append()不同的是，新增的数据会作为一个独立的元素，此函数会改变JSON数组中元素的个数，函数语法为：json_arry_insert(json_doc, path, val [,path, val] …)
select * from js;
update js set val=json_array_insert(val,'$[0]','x', '$[3]','y', '$[100]','z') where id=1;
select * from js;
- json_array_insert(val,'$[0]','x', '$[3]','y', '$[100]','z')，插入了3个元素，JSON数组的元素由4个增加至7个
- 第一个元素插入指定位置后，从这个位置开始，所有元素向后移动一位，这会改变后续元素编号，并影响后续的插入位置
- '$[0]'位置插入'x'后，所有元素后移一位，"a"的位置由'$[2]'变成了'$[3]'，因此函数第二个插入'$[3]'是插在"a"的前面，而不是原'$[3]'元素"b"的前面
- 函数第三个元素'$[100]'，超出了数组长度，因此附加在数组的最后
2.2.3 json_insert()函数
json_insert() 函数可以对JSON数组或JSON对象新增元素，根据给定的path，如果元素不存在，则进行新增，如果元素已存在，则忽略，不做任何操作，即只新增不更新。语法为：json_insert(json_doc, path1, val1, [path2, val2 …])
select * from js where id=1;
select json_insert(val,'$[0]','x','$[4]','y') from js where id=1;
- 对JSON数组操作时，第一个path '$[0]'，指定插入'x'，但因'$[0]'已存在，因此忽略
- 第二个path '$[4]'，指定插入'y'，由于原数组只有4个元素，最大编号只到'$[3]'，新增成功
对json对象操作时同样，已存在的键值忽略，不存在的键值新增：
select * from js where id=2;
select json_insert(val,'$.key1','x','$.key3','y') from js where id=2;
- JSON对象中键key1已存在，因此新值'x'被忽略
- JSON对象中键key3不存在，因此新键值对{"key3":"y"}新增成功
2.3 JSON数据更新
JSON数据的更新，你可以把JSON作为一个字符串，更新完后再整体赋值回去，但这种方法在JSON对象较大的时候可能效率较低，且解析成本也高。MySQL提供了json_replace()函数可以高效的完成指定路径上的元素更新。
2.3.1 json_replace()函数
json_replace() 函数可以对JSON数据进行原地（in-place update）更新，即用新的值替换旧值，其语法为 json_replace(json_doc, path1, new_val1, [path2, new_val2 …])，此函数仅对已存在的值进行更新，对不存在的值直接忽略，即只更新不新增。
将json数组前两个值由1和2更新为'x'和'y':
select * from js where id=1;
update js set val=json_replace(val,'$[0]','x','$[1]','y','$[5]','z') where id=1;
select * from js where id=1;
- josn_replace函数通过'$[0]'和'$[1]'指定第1，2个元素
- 这里$[5]指定更新了一个不存在的元素，因此忽略
同样，对于josn对象，只需要将path的写法换成'$.key'格式即可，下面将key1,key2的值分别更新为'value1'和'value2':
select * from js where id=2;
update js set val=json_replace(val, '$.key1', 'value1', '$.key2','value2', '$.key3','value3') where id=2;
select * from js where id=2;
- 这里提供了一个不存在的'$.key3'，所以被忽略
2.4 JSON数据同时新增和更新
如果需要同时进行更新和新增（例如数据同步），利用json_set() 函数可以同时完成对数据的更新和新增，对于已存在的元素更新，不存在的元素新增。在功能上类似MySQL的replace into或Oracle的merge into语句。
2.4.1 json_set()函数
json_set() 函数语法为：json_set(json_doc, path1, new_val1, [path2, new_val2 …])
JSON数组中，同时更新和新增：
select val from js where id=1;
select json_set(val,'$[0]','x','$[100]','z') from js where id=1;
- 第一个path参数'$[0]'，匹配数组中第一个元素，将1更新为'x'
- 第一个path参数'$[100]'，匹配数组中第100个元素，由于不存在，新增到数组最后
JSON对象中，同时更新和新增：
select val from js where id=2;
select json_set(val,'$.key1','x','$.key3','z') from js where id=2;
- 第一个path参数'$key1'，匹配对象中键为'key1'的，将其值更新为'x'
- 第二个path参数'$key3'，未匹配到键，直接新增键值对
json_insert(), json_replace(), json_set() 3个函数的参数格式是完全相同的，但功能不同，区别总结如下：
- json_insert() 仅新增，不更新
- json_replace() 仅更新，不新增
- json_set() 既更新又新增，相当于上面两个函数合体
2.5 JSON数据删除
json_remove() 函数从JSON数据中删除指定的元素，语法为json_remove(json_doc, path1 [,path2 …])，对于提供多个path参数的，每次删除后都会重新评估元素的位置。
2.5.1 json_remove()函数
从JOSN数组中删除指定位置的元素：
select val from js where id=1;
select json_remove(val,'$[1]','$[2]') from js where id=1;
- 这里指定删除了第2，第3个元素，对应位置是'$[1]','$[2]'
- 注意在删除'$[1]'位置的元素后，所有后续元素编号都向前移动1位，因此'$[2]'删除的是新'$[3]'位置的元素"b"，而不是原'$[3]'位置的元素"a"
对于JSON对象，只需指定要删除的键即可：
select val from js where id=2;
select json_remove(val,'$.key1') from js where id=2;
除了json_remove()函数，json_merge_patch()在对JSON对象某个键最后出现的值赋予null时，也可以删除该键值对（后面会提到）。
2.6 JSON数据合并
MySQL提供了两个函数可以合并多个JSON格式的数据：
- json_merge_preserve()
- json_merge_patch()
两个函数的行为略有不同，可以总结为json_merge_preserve()会保留重复值，json_merge_patch()会替换合并。
2.6.1 json_merge_preserve()函数
joson_merge_preserve() 在合并JSON数组时，会把所有元素简单的拼接成一个大数组：
SELECT json_merge_preserve('[1, 2]', '[true, false]','["a","b"]');
SELECT json_merge_preserve('[1, 2]', '{"key1": "value1"}');
- 2个示例中，所有的元素都保留了下来
如果合并的是JSON对象，json_merge_preserve()函数会保留所有非重复键值对，对于重复的键，会将其值合并为JSON数组保留下来
SELECT json_merge_preserve('{"key1": "value1"}','{"key2": "value2"}');
SELECT json_merge_preserve('{"key1": "value1"}','{"key2": "value2","key1":"value3"}') duplicate_key1;
SELECT json_merge_preserve('{"key1": "value1"}','{"key2": "value2","key1":"value3"}','{"key1": null}') last_value_is_null;
- 第2,3语句中，键"key1"均出现了多次，但所有的值都被合并成数组保留了下来
2.6.2 json_merge_patch()函数
joson_merge_patch() 在合并JSON数组时，会将每个参数视为独立的元素，并应用"最后的值胜出"原则，只选择最后一个元素，因此只要函数中包含JSON数组，只有最后一个值会保留下来：
SELECT json_merge_patch('[1, 2]', '[true, false]','["a","b"]');
SELECT json_merge_patch('[1, 2]', '{"key1": "value1"}');
SELECT json_merge_patch('{"key1": "value1"}','{"key2": "value2"}','[1, 2]');
- 3个示例中，都是只有最后一个元素被保留了下来
如果合并的都是JSON对象， json_merge_patch()函数会保留所有非重复键值对，对于重复的键，只保留最后出现的值（但最后出现的值不能为null，否则会删除该键值）：
SELECT json_merge_patch('{"key1": "value1"}','{"key2": "value2"}');
SELECT json_merge_patch('{"key1": "value1"}','{"key2": "value2","key1":"value3"}') duplicate_key;
SELECT json_merge_patch('{"key1": "value1"}','{"key2": "value2","key1":"value3"}','{"key1": null}') last_value_is_null;
- 第2个语句中，key1出现了2次，保留了最后出现的"value3"
- 第3个语句中，key1出现了3次，但最后出现的值为null，所以该键值被删除
如果最后的键值为null会删除该键值对，利用这个特性我们可以给json_merge_path可以用传入null值来删除指定键值对（也可以用json_remove）。
2.7 判断元素否存在
某些时候，我们并不需要提取数值，而仅仅想知道某个元素是否存在（例如作判断条件时），下列函数可以实现此类功能：
- json_contains_path() 测试某个path是否存在
- json_search() 通过值查询其具体path
- value member of(json_array) 通过值测试是否存在于JSON数组中
2.7.1 json_contains_path()函数
josn_contains_path()函数可以用来测试指定的一个或多个path是否存在。语法为：json_contains_path(json_doc, one_or_all, path [,path …])，当path存在时返回1，不存在时返回0（不是null）。
该函数第二个参数one_or_all有2种取值，代表2种测试行为：
- one, 至少有1个path存在即返回1
- all, 所有path都存在才返回1
判断指定path在数组中是否存在：
set @array1 = '["a", "b", "c"]';
set @object1 = '{"key1":"value1", "key2":"value2"}';
select json_contains_path(@array1, 'one', '$[0]', '$[100]');
select json_contains_path(@array1, 'all', '$[0]', '$[100]');
- 这里有1个不存在的path '$[100]'，根据第二个参数one_or_all，这里分别返回1和0
判断指定path在对象中是否存在：
select json_contains_path(@object1, 'one', '$.key1', '$.key100');
select json_contains_path(@object1, 'all', '$.key1', '$.key100');
json_contains_path()函数用来判断指定的path是否存在，如果想判断指定的元素(值)是否存在，可以用json_contains()函数，它可以用来测试两个对象的包含关系，当然也可以测试值。
2.7.2 json_search()函数
json_search()函数可以通过值来查询path，如果存在则返回其具体的path，不存在则返回null。语法为：json_search(json_doc, one_or_all, search_str [escape_char [,path]…)，
第二个参数one_or_all控制返回path的搜索行为：
- one, 返回第一个匹配的path，当找到第一个匹配的path时搜索即终止
- all, 返回所有匹配的path
在提供search_str时，可以用%或_来代替任意多个或单个字符（和like中用法一样）。但是如果要匹配'%'或'_'字符本身，则要加上转义字符，即后面的参数escape_str，省略该参数或提供null值，则默认为\
set @array1 = '[1,2, 1, "abc", "abd", "xyz"]';
set @object1 = '{"key1":"abc", "key2":"abd"}';
select json_search(@array1, 'one', 'abc');
select json_search(@array1, 'all', 'ab%');
- 第二个select采用模糊匹配，'ab%'配合参数'all'返回所有以ab开头的字符串的path
这里我想查找字符串中包含1个%的元素的path:
set @array2 = '["a%c", "a%%c"]';
select json_search(@array2, 'all', 'a%c');
select json_search(@array2, 'all', 'a\%c');
- 第一个select中，'a%c'中的'%'会被理解成通配符，因此'a%%c'的path也返回了
- 但我们只想匹配'%'字符本身，只需要用转义字符'\'转义即可，这也是第四个参数escape_char，这里省略了，默认就是'\'
若省略第四个参数escape_char，或者使用null，默认都是'\'，也可以显式指定其他转义字符
select json_search(@array2, 'all', 'a\%c',null);
select json_search(@array2, 'all', 'a@%c','@');
第五个可选参数path，可以用来测试指定path上是否是某值：
select @array1;
select json_search(@array1,'all','abc',null,'$[3]');
select json_search(@array1,'all','abc',null,'$[4]');
- 这里提供了第五个参数path，两个select分别测试指定位置上值是否是'abc'
2.7.3 value member of(json_array)
对于JSON数组，如果只是想知道某个值是否存在，还可以用value member of(json_array)来判断值是否包含在指定数组中（MySQL 8.0.17后可用），包含返回1，不包含则返回0：
select val, 2 member of(val), 'x' member of(val) from js where id=1;
这种方式相对于上面的函数要简练许多，但功能也相对单一。
2.8 判断JSON对象之间的关系
当我们需要判断2个JSON对象间的关系，例如包含，重叠，可以利用json_contains()或json_overlaps()函数来完成
2.8.1 json_contains()函数
json_contains() 可以测试两个JSON对象间是否是包含关系（也可以来测试值是否存在），其语法为：json_contains(doc, candiate [, path])，该函数会测试candidate是否包含在doc中，如果提供了第三个可选参数path，那么即限定doc是否在path指定的路径处包含candiate，如果包含则返回1，不包含则返回0.
关于包含关系的测试，根据测试对象是JSON数组和对象，还存在下列规则：
- 对于JSON数组，candidate中每个元素都包含在doc中，即是包含
- 如果提供了path，那么candiate会被视为一个整体，必须在指定path存在才被视为包含
- 对于JSON对象，candidate的键必须包含在doc的键中，且值被对应的键的值包含，才视为包含
set @array='[[1,"z"], 2, "a", "b", 1, "z"]';
set @candidate1='[2, "b"]';
set @candidate2='[1, "z"]';
select json_contains(@array, @candidate1);
select json_contains(@array, @candidate2, '$[1]');
select json_contains(@array, @candidate2, '$[0]');
- 第一句select，"2", "b" 分别包含在@array的元素中（独立包含），因此返回1
- 第2,3句select，由于提供了path参数，因此数组被视为1个整体，只有在指定path包含才算作包含
对于JSON对象，
set @object='{"key1":["x","y"], "key2":2, "key3":3}';
set @candidate3='{"key1":"x", "key3":3}';
set @candidate4='{"key1":"z", "key3":3}';
select json_contains(@object, @candidate3);
select json_contains(@object, @candidate4);
- 第1句select，@candidate3的键和值都包含在@object中，返回1
- 第2句select，@candidate4的键都包含在doc中，但key1的值"z"不包含在@object中，因此返回0
2.8.2 json_overlaps()函数
相对于json_contains函数，json_overlaps只判断两个JSON对象是否包含相同的元素或键值对，包含则返回1，不包含则返回0，语法为：json_overlaps(json_doc1, json_doc2)
判断数组是否包含相同的元素（即是否有重叠）：
set @array1 = '[1, 2]';
set @array2 = '[1, 3]';
set @object1 = '{"key1":"value1", "key2":"value2"}';
set @object2 = '{"key1":"value1", "key3":"value3"}';
set @object3 = '{"key1":"x", "key3":"value3"}';
select json_overlaps(@array1, @array2);
判断对象是否包含相同键值对（是否重叠）
select json_overlaps(@object1, @object2);
select json_overlaps(@object1, @object3);
- 对于JSON对象，必须键值对都相同才算相同的元素
- @object1和@object3包含相同的键，但是值不同，因此不算重叠
三、JSON数据与索引
在MySQL的表中，JSON类型的列通常无法直接建立索引，你可以用虚拟生成列(Virtual Generated Columns），并根据该列来建立间接索引。但是在MySQL8.0.17版本后，对于JSON数组（JSON对象不行），可以建立多值索引（Multi-valued Index）。
3.1 生成列索引（Generated Column Index）
对于JSON数据类型需要建立索引，可以对将经常查询的元素提取出来，作为一个虚拟的生成列，并在该列上建立索引，查询时通过虚拟列上索引即可快速定位数据。
虚拟列的语法是：
col_name data_type [GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']- expr 是列的生成表达式，需要依赖其他列计算
- virtual 代表该列不实例化，不消耗存储空间，每次用到该列时计算
- stored 代表实例化存储，消耗存储空间，且每次更新其依赖列时，都会同时更新虚拟列数据
下面示例，b是根据a生成的虚拟列，并且在b上建立了索引idx：
create table person(
a json,
b int generated always as (a->"$.id"),
key idx(b));
insert into person(a) values('{"id":1, "name":"Vincent"}'), ('{"id":2, "name":"Victor"}'), ('{"id":3, "name":"Grace"}');
这样就可以通过b列查询时即可利用索引提速，快速定位记录：
explain select * from person where b=2;
3.2 多值索引（Multi-valued Index）
MySQL 8.0.17版本引入了多值索引，可以直接对JSON类型列创建索引，但是仅限JSON数组。在传统二级索引中，一个索引记录对应一条数据记录。但在多值索引中，会根据JSON数组中的值建立多个索引，同时指向这一条记录，其底层原理依然是通过虚拟列完成的，只是对用户来说这一步是透明的。
在对JSON数组列建立索引前，需要先用cast(… as type array)将其由JSON数组类型转换为SQL数组类型：
create table t(
a json,
key idx((cast(a as unsigned array)))
);
insert into t values('[1, 2, "3", 4]'), ('[5, "6", 7]'),('[8, 9, 10]');
- 注意create table语句中cast函数外面需要额外一层括号
- 由于这里限制的是unsigned array，因此json数组中元素必须可以转换为数字，例如插入字符"a"则会报错
- 其他可能的type类型还有:date, datetime, decimal, double, signed, time, year等
在建立多值索引后，member of(), json_contains(), json_overlaps()函数在where条件中则可以利用多值索引来加速查询。
这里查询a列中包含3的记录，通过执行计划可以发现member of()函数使用了多值索引idx：
select * from t where 3 member of(a);
explain select * from t where 3 member of(a);
四、将JSON转换为结构化数据
JSON是一种非结构化数据，不需要像数据库表那样预先定义列。有时我们需要又需要将JSON数据转换为结构化数据，存储到数据库的表中。
MySQL提供了json_table()函数，可以将JSON数据转换成类似表一样的结构化数据，转化后的数据类似一张虚拟表，可以直接用select查询或者与其他表连接。
4.1 json_table()函数
json_table()的语法为：
JSON_TABLE(
expr,
path COLUMNS (column_list)
) [AS] alias
column_definition:
name FOR ORDINALITY
| name type PATH string path [on_empty] [on_error]
| name type EXISTS PATH string path
| NESTED [PATH] path COLUMNS (column_list)
on_empty:
{NULL | DEFAULT json_string | ERROR} ON EMPTY
on_error:
{NULL | DEFAULT json_string | ERROR} ON ERROR部分语法解释：
- expr 即要转换的JSON源数据
- path 是要转换的JSON数据范围，expr + path定义了转换的数据
- column_definition是每一列的详细定义，共4种
- name for ordinality 为每一行数据生成一个编号，从1开始，相当于一个auto_increment列
- name type path string_path [on empty] [one_error] 指定某列数据的具体path，[on_empty] [on_error]逻辑可以参照上面的json_value()函数
- name type exists path string path 根据指定位置是否有值将1和0放入name 列中
- nested [path] path columns(column_list)
下面示例将由JSON对象组成的数组转化为表：
set @array1 = '[ {"name":"Vincent", "age":30, "salary":1000}, {"name":"Victor", "age":6}, {"name":"Grace", "age":30, "salary":3000}]';
select * from
json_table(
@array1,
'$[*]' columns(
id for ordinality,
name varchar(32) path '$.name',
age int path '$.age',
salary decimal(6,2) path '$.salary' default '0' on empty,
have_salary int exists path '$.salary')
) as person;
- id for orinality 为每行数据生成一个编号，从1开始
- salary 列default '0' on empty，在对象没有'$.salary'键时，默认赋值0
- have_salary 通过判断 '$.salary'是否存在，分别显式1或0
以上即是MySQL中JSON的主要用法总结，覆盖了日常应用中常见处理，应用好JSON类型的数据管理可以大幅提升MySQL处理非结构化数据的能力。
![[足式机器人]Part2 Dr. CAN学习笔记- 最优控制Optimal Control Ch07](https://img-blog.csdnimg.cn/direct/40f20d6d28c74c3a981944c5315bfd56.png#pic_center)
