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

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处理非结构化数据的能力。

V1ncent Chen

关注

11
点赞
踩
21

收藏

觉得还不错? 一键收藏
0
评论
MySQL JSON数据类型全解析（JSON datatype and functions）

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

扫一扫