MySQL JSON操作介绍

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倍以上。因此具体使用上,大家还是根据各自业务需求做数据库选项。

关于chenzujie

非著名码农一枚,认真工作,快乐生活
此条目发表在数据库分类目录。将固定链接加入收藏夹。

发表评论

邮箱地址不会被公开。 必填项已用*标注