mysql02-mysql的json操作函数

json函数

　　1.json_extract 抽取　　　　

　　select json_extract('[10, 20, [30, 40]]', '$[1]');

 

 

select

json_extract(data, '$.name'),

json_extract(data, '$.address')

from json_user;

 

　　JSON_OBJECT 将对象转为 json

 

select json_object("name", "enjoy", "email", "enjoy.com", "age",35);

insert into json_user values (null,json_object("name", "enjoy", "email", "enjoy.com", "age",35) );

 

 

　　son_insert 插入数据

　　语法：JSON_INSERT(json_doc, path, val[, path, val] ...)

 

set @json = '{ "a": 1, "b": [2, 3]}';

select json_insert(@json, '$.a', 10, '$.c', '[true, false]');

update json_user set data = json_insert(data, "$.address_2", "xiangxue") where uid = 1;

 

 

　　json_merge 合并数据并返回

 

 

select json_merge('{"name": "enjoy"}', '{"id": 47}');

select

json_merge(

json_extract(data, '$.address'),

json_extract(data, '$.address_2'))

from json_user where uid = 1;

 

　　其他函数：https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

　　

　　JSON 索引：

JSON 类型数据本身 无法直接 创建索引，需要将需要索引的 JSON 数据 重新 生成虚拟列

(Virtual Columns) 之后，对 该列 进行 索引

 

create table test_inex_1(

data json,

gen_col varchar(10) generated always as (json_extract(data, '$.name')),

index idx (gen_col)

);

insert into test_inex_1(data) values ('{"name":"king", "age":18, "address":"cs"}');

insert into test_inex_1(data) values ('{"name":"peter", "age":28, "address":"zz"}');

 

select * from test_inex_1;