【MySQL】JSON 格式字段处理

MySQL 5.7 版本后已支持 JSON 格式，这虽是 MySQL 的一小步，但可以说是程序开发的一大步，再也不用将 JSON 内容塞到 VARCHAR 类型字段了，程序设计也会变得更加灵活。网上大多只针对JSONObject 对象类型，本文也将详解 JSONArray 数组类型。

1 定义

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，它以键值对的方式存储数据，并以大括号和方括号进行标记，MySQL 的 JSON 格式字段可以存储 JSON 对象和数组。

JSONObject：对象

{
  "name": "yinyu",
  "age": "18"
}

JSONArray：数组

["yinyu", "tom", "jack"]

而且对象和数组都可以嵌套其他对象或数组，从而形成复杂的数据结构，比如 👇

[
  {
    "name": "yinyu",
    "age": [18, 28, 38]
  },
  "tom",
  "jack"
]

对于对象来说，它的键只能为字符串，值类型支持 null，string，boolean，number，object，array 等。

建表 SQL

在创建表时，可以指定字段类型为 JSON ，不过无需指定 JSON 类型的长度，因为默认值只能为 null 。此外，JSON 字段类型可以根据实际存储的数据自动推断是对象还是数组结构，因此无需显式指定。

create table `test_json_tb` (
  `id` bigint(20) not null auto_increment,
  `json_obj` json default null comment 'json 对象字段',
  `json_arr` json default null comment 'json 数组字段',
  primary key (`id`)
) engine=innodb default charset=utf8mb4;

插入数据 SQL

insert into test_json_tb(json_obj, json_arr) values
('{"name":"yinyu", "age":18, "tags":["rap", "dance"]}', '["yinyu", "yinyu1", "yinyu2"]'),
('{"name":"tom", "age":19, "tags":["rap"]}', '["tom"]'),
('{"name":"jack", "age":20, "tags":["dance"]}', '[{"name":"jack"}, {"age":"20"}]');

建表如图 👇，接下来将使用该数据表来教学 MYSQL 的 JSON 常用函数。

2 原生查询

原生查询规则

针对 JSONObject 对象的规则：json对象字段名->’$.json属性名’

针对 JSONArray 数组的规则：json数组字段名->’$[数组索引]’  or  json对象字段名->’$数组的键名[数组索引]’

示例：

select
	json_obj->'$.name' name,
	json_obj->'$.tags[0]' tags0,
	json_arr->'$[0]' arr0
from test_json_tb;

可以看到查询到的字段字符串类型有个双引号，那么可以通过将 -> 替换成 ->> 去除，转义符同时也会去除。

条件查询

那么我们使用原生查询规则来试下条件查询，比如 👇

select *
from test_json_tb
where json_obj->'$.name' = 'yinyu';

由于 JSON 字段的模糊搜索仅支持 %str% 格式，因此它的模糊搜索时索引无效：

select * 
from test_json_tb 
where json_obj->'$.name' like '%yin%';

对于联表查询、多条件查询均是支持的~

3 常用函数

JSONObject 对象类型和 JSONArray 数组类型基本上都可以使用以下函数，而网上攻略大多只针对JSONObject 对象类型，因此我补充了 JSONArray 数组类型的相关规则和示例。

① JSON_EXTRACT() 提取

规则和原生查询类型~

针对 JSONObject 对象类型，规则：json_extract(对象字段名, '$.属性名')

针对 JSONArray 数组类型，规则：json_extract(数组字段名, '$[数组索引]') or json_extract(对象字段名, '$.数组的键名[数组索引]')

select id,
	json_extract(json_obj,'$.name') as name,
	json_extract(json_obj,'$.tags[1]') as tags1,
	json_extract(json_arr,'$[0]') as arr0
from test_json_tb;

若想去除双引号，可以使用 JSON_UNQUOTE(JSON_EXTRACT())。

② JSON_SET() 更新

将数据插入到 JSON 格式字段中，如果是 JSONObject 对象类型，则有 key 则替换，无 key 则新增；如果是 JSONArray 数组类型，则根据索引进行替换或新增，规则以 JSONObject 对象类型为例。

规则：JSON_SET(json数据, '$.属性名', '更新/插入的值/数组', '$.数组的键名[数组索引]', '更新/插入的值'......)

示例：比如修改第2条数据，将他的 age 修改为 20，并且 tags 增加一个 “dance”：

update test_json_tb t1
set json_obj = json_set(t1.json_obj,'$.age',20, '$.tags[1]', 'dance') 
where id=2;

更新后的记录 👇

③ JSON_INSERT() 插入

和 JSON_SET() 类似，但 JSON_INSERT() 只插入不更新（有 key 保持原样）。

规则：JSON_INSERT(json数据, '$.属性名', '更新/插入的值/数组', '$.数组的键名[数组索引]', '更新/插入的值'......)

示例：比如给第3条数据新增一个 age 属性和 from 属性，但是执行 sql 后会发现 age 属性插入是不生效的，这是因为 age 属性已存在，而 from 属性新增成功。

update test_json_tb t1
set json_obj = json_insert(t1.json_obj,'$.age', 21,'$.from', 'china') 
where id=3;

④ JSON_REPLACE() 替换

JSON_REPLACE() 的作用就是只替换/更新，不插入，针对JSONObject 对象类型，则有 key 则替换，无 key 则保持原样；如果是 JSONArray 数组类型，则根据索引进行替换，规则以 JSONObject 对象类型为例。

规则：JSON_REPLACE(json数据, '$.属性名', '替换的值/数组', '$.数组的键名[数组索引]', '替换的值'......)

示例：接着上边第3条记录，给 age 属性的值替换成 21，tags 数组的第一个值替换成 “rap”

update test_json_tb t1
set json_obj = json_replace(t1.json_obj,'$.age', 21,'$.tags[0]', 'rap') 
where id=3;

注意：如果该属性或数组索引不存在，那么是不会进行替换的，和 JSON_INSERT() 正好是反着来，而 JSON_SET() 集合了这两者。

⑤ JSON_REMOVE()  移除

顾名思义，该函数的作用是从删除 JSON 数据。

规则：JSON_REMOVE(json数据, '$.属性名', '$.数组的键名[数组索引]')

示例：之前不是给第3条记录增加了 from 属性么，我们来移除它，并且移除 json_arr 字段的第二个属性。

update test_json_tb t1
set json_obj = json_remove(t1.json_obj,'$.from'),
	json_arr = json_remove(t1.json_arr,'$[1]') 
where id=3;

⑥ JSON_OBJECT()、JSON_ARRAY()

这两个函数分别对应 JSON 对象和 JSON 数组，分别用来创建 JSON 对象和 JSON 数组，可以搭配 JSON_SET()、JSON_INSERT()、JSON_REPLACE() 等函数，也可用于查询等操作。

规则：JSON_OBJECT(键名, 值, 键名, 值......)、JSON_ARRAY(元素、元素、元素......)

接下来以插表 SQL 作为示例：

insert into test_json_tb(json_obj, json_arr) values
(json_object('age',22,'name','mike','tags',json_array('sing')), 
 json_array(11,22,33));

⑦ JSON_CONTAINS() 是否包含

校验 JSON 数据中是否包含特定值（可以是属性、对象、数组、数组索引等）。

规则：JSON_CONTAINS(target, candidate[, path])，详情看示例

示例1：查询出 json_obj 字段中包含 age = 18 的记录

select * from test_json_tb 
where json_contains(json_obj, json_object('age',18))

示例2：查询出 json_arr 字段中包含 11 的记录

select * from test_json_tb 
where json_contains(json_arr, json_array(11))

示例3：查询出 json_obj 字段中 tags 属性中同时包含 “rap” 和 “dance” 的记录

select * from test_json_tb 
where json_contains(json_obj, json_array('rap','dance'), '$.tags')

⑧ JSON_TYPE(）类型

查询某个 JSON 字段属性类型。

规则：JSON_TYPE(原生查询或 JSON_EXTRACT())

示例：查看 json_obj 字段中 tags 属性的类型

select json_obj->'$.tags' ,json_type(json_obj->'$.tags') as type 
from test_json_tb 
-- or
select json_extract(json_obj,'$.tags') ,
	   json_type(json_extract(json_obj,'$.tags')) as type 
from test_json_tb 

⑨ JSON_KEYS() 键名

查询 JSON 数据中的所有键/key，返回列表，针对 JSON 对象，因为数组是没有键的。

规则：JSON_KEYS(json_value)

select json_keys(json_obj) 
from test_json_tb 

⑩ JSON_SEARCH()  深度查询

JSON_SEARCH() 函数，简单来说就是加强版查询，其实 JSON_EXTRACT() + limit 等 SQL也能达到效果。

规则：JSON_SEARCH(json数据，one/all，json 查询规则)

one 是返回1条记录，all 是返回符合条件的所有记录

示例1：查询路径--以 json 对象为例

select json_search(json_obj,'all','yinyu', null)
from test_json_tb

他会返回符合条件的记录里的路径，若是路径编写有困难，那么可以试下这个深度查询！

null -- 若搜索内容不存在，则返回 NULL，第三个参数为值。

示例2：条件查询--针对 json 对象（第五个参数为路径，条件查询时搭配  is not null）

查询 json_obj 字段中 name 属性为 “yinyu” 的记录

select *
from test_json_tb
where json_search(json_obj,'all','yinyu', null,'$.name') is not null

示例3：条件查询--针对 json 数组

查询 json_arr 字段中第一个元素为 “yinyu” 的记录

select *
from test_json_tb
where json_search(json_arr,'all','yinyu', null,'$[0]') is not null

示例4：模糊查询

select *
from test_json_tb
where json_search(json_obj,'all','%t%', null,'$.name') is not null

---

总结

本文完善了 JSON 数组的相关操作，大家如果有疑问都可以评论提出，有不足之处请大家批评指正，希望能多结识这方面的朋友，共同学习、共同进步。