MYSQL JSON 类型操作总结

-- 提取json字段中的值------------------------------------------
-- 方法1
SELECT id, ename, JSON_EXTRACT(info, '$.sal') AS salary, JSON_EXTRACT(info, '$.job') AS job FROM tb_emp
-- 方法2
SELECT id, ename, info->'$.sal' AS salary, info->'$.job' AS job FROM tb_emp

-- 判断数据josn字段中是否包含某个json值
SELECT JSON_CONTAINS(info, '{"ename": "SCOTT"}')  FROM tb_emp WHERE id = 8

-- 判断数据json字段中是否包含给定key的任意一个
SELECT JSON_CONTAINS_PATH(info, 'one', '$.ename', '$.dno') FROM tb_emp WHERE id = 8

-- 判断数据json字段中是否同时包含给定的key
SELECT JSON_CONTAINS_PATH(info, 'all', '$.ename', '$.deptno') FROM tb_emp WHERE id = 8

-- 作为查询条件使用----
-- 使用提取方法后与常规字段使用一致
SELECT id, ename,  info->'$.sal' AS salary, info->'$.job' AS job FROM tb_emp WHERE info->'$.sal' > 1500


-- 返回不带引号的json value
SELECT id, ename, info->>'$.job' AS job FROM tb_emp

-- JSON_OBJECT() 的使用
-- 将键值对转换为json对象
SELECT JSON_OBJECT("name", "张三")
-- 配合JSON_CONTAINS（）使用，判断json字段中是否包含某个键值对
SELECT JSON_CONTAINS(info, JSON_OBJECT('ename', 'SCOTT'))  FROM tb_emp WHERE id = 8

-- json数组相关
-- json对象内包含json数组字段
SELECT id, json_value->>'$.deptName[0]' FROM dept
-- 字段直接为json数组
SELECT id, json_value->>'$[1]' FROM dept

-- JSON_ARRAY 的使用
SELECT JSON_ARRAY(JSON_OBJECT("name", "1"), JSON_OBJECT("name", "2"))

-- JSON_TYPE() 查看json字段类型
SELECT json_value->'$.deptName' ,JSON_TYPE(json_value->'$.deptName') as type from dept 

-- JSON_KEYS 返回key集合
SELECT JSON_KEYS(json_value) FROM dept 

-- JSON_SET('原来的json数据', '要写入的键值对'...) ,原来有key则覆盖，原来无key则新增
update dept set json_value=JSON_SET('{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}','$.deptName','新增的部门1','$.newData','新增的数据') WHERE id=2;

-- JSON_INSERT(json_doc, path, val[, path, val] ...)
UPDATE dept set json_value=JSON_INSERT('{"a": "1", "b": "2"}', '$.deptName', '新增的部门2','$.newData2','新增的数据2') 
WHERE id=2

-- JSON_REPLACE(json_doc, path[, path] ...) 更新sql内部键值对
UPDATE dept set json_value=JSON_REPLACE('{"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "新增的数据2"}', '$.newData2', '更新的数据2') WHERE id =2;

-- JSON_REMOVE(json_doc, path[, path] ...) 删除sql内部的键值对
UPDATE dept set json_value=JSON_REMOVE('{"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "更新的数据2"}','$.b') WHERE id =2;