MySQL5.7中JSON系列操作函数

前言

在工作中，遇到这种情况，某些字段存储的是josn格式，之前的做法是先查出数据然后进行json_decode()处理，然后再进行相关操作；最近发现了MySQL也支持一些json系列的操作函数，所以记录下来，以后可以更加方便快捷对此类数据进行灵活操作。

带有json格式字段的数据表

注：以下函数仅针对MySQL5.7及以上版本；如果SQL语句报错，请先查看MySQL版本。

 

JOSN相关查询操作

（1）JSON_CONTAINS -- 获取josn字段中包含某一项的数据

-- 查询"projext_type" 字段中有"博士项目"的数据
SELECT * FROM crm_test WHERE JSON_CONTAINS(project_type, '["博士项目"]');

查询结果： 

 

 （2）JSON_SEARCH  -- JSON_SEARCH(字段,[ 'one'：第一个符合条件 | 'all'：所有符合条件],查找的条件)

-- 查询"project_type"字段中以 "专业"开头的字段
SELECT * FROM crm_test WHERE JSON_SEARCH(project_type,'one','专业%') IS NOT NULL

查询结果：

（3） JSON Path -- 精准获取josn中的某个数据

-- 获取project_type字段中 josn数组的第二个元素（注：下标从0开始）
SELECT project_type->>"$[1]" as 'type' FROM crm_test 

查询结果：

（4）JSON_UNQUOTE -- 获取json字段中的某一项

-- 获取id为6的name,和project_type 字段中的 cphone字段
select name,JSON_UNQUOTE(project_type->'$.cphone') as cphone from crm_test where id=6


-- 等价于
select name,project_type->>'$.cphone' as cphone from crm_test where id=6

数据结构：

查询结果：

 

JOSN相关更新操作

（1）JSON_MERGE -- 追加

-- 将id为5的 project_type 字段追加 "合作办学"
UPDATE crm_test SET project_type = JSON_MERGE(project_type,'["合作办学"]') WHERE id = 5;

 效果前：

效果后： 

（2）JSON_SET -- 更改（替换已经存在的值，增加不存在的值）

-- 将id为3的，josn第一个元素 更改为"同等学力"
UPDATE crm_test SET project_type = JSON_SET(project_type, '$[0]','同等学力') WHERE id = 3;

效果前：

效果后： 

（3）JSON_INSERT  -- 插入新值（新增不存在的值）

-- 将id为3的project_type 新增"合作办学"
UPDATE crm_test SET project_type = JSON_INSERT(project_type, '$[1]','合作办学') WHERE id = 3;

-- JSON_INSERT(json_doc, path, val[, path, val] ...)
-- json_doc 是JSON字符串
-- path 是要插入数据或更新值的元素的路径
-- val 是新的值

效果前：

 

效果后：

（4）JSON_REPLACE -- 替换（替换/修改已经存在的值）

-- 将id为1的project_type 字段的第一个元素替换成"同等学力"
UPDATE crm_test SET project_type = JSON_REPLACE(project_type, '$[0]','同等学力') WHERE id = 1;

效果前：

效果后： 

（5）JSON_ARRAY_APPEND -- 向指定位置尾部追加数据

UPDATE crm_test SET project_type = JSON_ARRAY_APPEND(project_type, '$[2]',"博士项目") WHERE id = 2;

效果前：

效果后：

（6）JSON_REMOVE -- 从指定位置移除数据

-- 将id为4的数据中 project_type josn数组中的第一个元素删除掉
UPDATE crm_test SET project_type = JSON_REMOVE(project_type, '$[0]') WHERE id = 4;

效果前：

效果后：