MySQL自版本5.7.8开始引入了对JSON的支持。毕竟随着JSON数据格式越来越流行,数据库字段类型支持JSON能给很多业务提供更大的便捷性,下面我们直接介绍对MySQL里JSON字段的增删改查操作。
建字段
首先我们建一张表testJson,里面有个jsonField的字段为json类型。
CREATE TABLE testJson (
id INT AUTO_INCREMENT PRIMARY KEY,
jsonField JSON
);
增
新增一个json数据
insert into testJson (jsonField)
values ('{
"school":"NO.1 Primary School",
"brief":"good class",
"class" : {
"grade" : "three",
"studentCount" : 30,
"teacher" : {
"name" : "John",
"gender" : "male"
}
},
"students" : [
{
"name" : "Jack",
"gender" : "male",
"age":13
},
{
"name" : "Tom",
"gender" : "male",
"age":13
},
{
"name" : "Marry",
"gender" : "female",
"age":14
}
]
}');
JSON里新增一个一级字段
UPDATE testJson SET jsonField = JSON_SET(jsonField, '$.headMaster', 'William') WHERE id = 1;
JSON里新增一个二级字段
UPDATE testJson SET jsonField = JSON_SET(jsonField, '$.class.courseNum', 5) WHERE id = 1;
JSON数组里新增一个元素
UPDATE testJson SET jsonField = JSON_ARRAY_APPEND(jsonField, '$.students', JSON_OBJECT('name', 'Cindy', 'gender', 'female', 'age', 12)) WHERE id = 1;
从上面的新增语句可以看出,新增JSON里的元素本质是用JSON_SET和JSON_ARRAY_APPEND 两个方法,一个用于新增字段,一个用于新增元素,然后通过$代表json最顶层位置去一级一级定位要新增字段的位置。
查
根据json里的字段查询
SELECT * FROM testJson WHERE JSON_EXTRACT(jsonField, '$.headMaster') = 'William';
根据json里的数组元素做查询
select jsonField ->'$.students[*]' from testJson where JSON_CONTAINS(jsonField->'$.students[*].name',JSON_ARRAY('Cindy'),'$');
上面的查询语句分别根据headMaster字段为William的全部数据,以及students里有name为Cindy的记录里的students数据。
第一个查询使用了JSON_EXTRCT方法,它会根据传的path路径($.headMaster)返回对应的数据,我们根据返回数据是否是William来过滤结果。
第二个查询使用的JSON_CONTAINS方法,该方法第一个参数表示用于做判断的内容,这里使用jsonField->’$.students[*].name’,表示要查询students的name字段,其中[*]表示students是数组内容,第二个参数是代表一个具体值用于匹配第一个参数的字段,这里使用JSON_ARRAY代表返回一个数组字段包含JSON_ARRAY里定义的数据。
改
修改JSON里一级数据
update testJson set jsonField=JSON_REPLACE(jsonField, '$.headMaster', 'Joe') where JSON_EXTRACT(jsonField, '$.headMaster') = 'William';
上面这条查询语句通过JSON_EXTRACT提取文档然后通过JSON_REPLACE方法做更新
修改JSON里的数组数据
UPDATE testJson
SET jsonField = JSON_SET(jsonField, '$.students', (
SELECT JSON_ARRAYAGG(
JSON_OBJECT('name', IF(element ->> '$.name' = 'Cindy', 'Clark', element ->> '$.name'), 'age', element ->> '$.age', 'gender', element ->> '$.gender')
)
FROM JSON_TABLE(
jsonField -> '$.students',
"$[*]" COLUMNS (
element JSON PATH '$'
)
) AS t
))
上面的修改语句本质是对students里有名为Cindy的改为Clark,整个修改语句用到了比较多的JSON相关方法。
我们首先看里面的
SELECT JSON_ARRAYAGG() FROM JSON_TABLE() as t
该方法显示通过JSON_TABLE构建了一个新的子表。
jsonField
是JSON字段的名称。$.students
是JSON路径,用于指定要提取的数据的位置,其中包含一个数组。$[*]
是JSON路径表达式,它指定了要提取的JSON数组的每个元素。在这里,我们希望提取数组的每个元素。element JSON PATH '$'
定义了一个名为”element”的列,它用于存储从JSON数组中提取的每个元素的值。
执行这个查询后,将返回一个包含从JSON数组中提取的数据的表如下:
接着我们通过JSON_ARRAYAGG重新生成一个JSON数组,而数组的元素内容就由JSON_OBJECT构造。
JSON_OBJECT('name', IF(element ->> '$.name' = 'Cindy', 'Clark', element ->> '$.name'), 'age', element ->> '$.age', 'gender', element ->> '$.gender')
)
JSON_OBJECT构造了有name,age,gender三个字段的元素,其中age和gender直接使用element里的age和gender,name字段用了一个IF判断,如果值为Cindy就改为Clark,否则使用element的name。到这里我们基本就实现了把Cindy改为Clark。最后用一个JSON_SET方法用于设置到students字段。整个修改语句的执行效率如下图。
删
删除JSON里的一级字段
update testJson set jsonField=JSON_REMOVE(jsonField, '$.headMaster') where id = 1;
删除JSON数组里的元素
UPDATE testJson
SET jsonField = JSON_REPLACE(jsonField, '$.students',
JSON_REMOVE(JSON_EXTRACT(jsonField, '$.students'),
JSON_UNQUOTE(JSON_SEARCH(JSON_EXTRACT(jsonField, '$.students[*].name'), 'all', 'Clark'))))
WHERE JSON_SEARCH(JSON_EXTRACT(jsonField, '$.students[*].name'), 'all', 'Clark') IS NOT NULL;
上面的删除语句是把students里名为Clark的学生给删除了。该语句也用到了比较多的JSON方法。首先看里面的JSON_SEARCH方法。
- 该方法第一个参数是要查询的内容,我们通过JSON_EXTRACT(jsonField, ‘$.students[*].name’)得到students数组里的所有name字段[‘Jack’, ‘Tom’, ‘Mary’, ‘Clark’]。
- 第二个参数all代表的是全部匹配,它只有两种值’one’,’all’,’one’代表只搜索第一个匹配的。
- 第三个元素代表要查找的内容。
通过上面的JSON_SEARCH我们会对应得到name为Clark的元素位置
select JSON_SEARCH(JSON_EXTRACT(jsonField, '$.students[*].name'), 'all', 'Clark') from testJson
类似上面的查询语句会得到”$[3]”,代表在数组里的第4个元素,但该函数返回的是个字符串,我们需要通过JSON_UNQUOTE做下处理去掉字符串变成$[3]。
然后使用JSON_REMOVE
- 该方法第一个参数是要删除的内容,我们通过JSON_EXTRACT(jsonField, ‘$.students’)提取了students数据。
- 第二个参数代表要删除的内容即上面得到的$[3]。
删除后的数据再通过JSON_REPLACE替换到students数据,最后就得到我们要的删除效果了。
结语
通过上面对增删改查的介绍,大家应该基本对MySQL怎么操作JSON有基础的认知,MySQL对JSON的支持整体来说丰富了整个数据库的能力,但我们也看到在处理一些JSON数组相关的内容时,整个MySQL语句还是比较复杂的。大家可以对比下MongoDB常用操作介绍,看看语法的简洁性以及对应的性能,纯操作JSON,MongoDB在性能上还是有极大优势,我自己做的测试,相同的要求,MongoDB处理json的效率比MySQL快基本10倍以上。因此具体使用上,大家还是根据各自业务需求做数据库选项。