比如extra字段格式化如下，下面有的例子会用到该Json
{
"type": "manual",
"task_id": 2000574,
"group_id": 83869,
"vehicle_type": "7",
"is_group_main": 1,
"batch_end_time": 1605754200,
"batch_start_time": 1605747000,
"merge_from_loginid": [],
"group_pattern_explain": {
"aoi_list": ["931MC000329", "931MC000029"],
"loginid_list": {
"40028482": "韩悌武"
},
"pattern_info": {
"931MC000029": {
"pattern": 2,
"grab_order": 1
},
"931MC000329": {
"pattern": 2,
"grab_order": 1
}
},
"ori_loginid_list": [40028482],
"group_main_loginid": "40107541"
},
"keys_group_pattern_explain": ["931MC0020"],
"010A": "aaa",
}
查询json中的某个字段
假如查询extra_json中中某个字段，查询如下
select * from result where extra_json->>'$.batch_start_time' <=1605754210 and extra_json->>'$.batch_end_time' >=1605754210
如果json里边有多层机构，可以使用如下结构查询
select extra_json->>'$.group_pattern_explain.ori_loginid_list' from result
json中常用的函数
json_set、json_insert、json_replace对比
json_set:替换
已经存在的值，增加不存在的值。
json_inset:新增不存在的值。
json_replace:替换/修改已经存在的值。mysql> select * from name; +----+------------------------------------------------+ | Id | extra | +----+------------------------------------------------+ | 1 | {"old": 12, "name": "xiaoyu", "weight": "abf"} | +----+------------------------------------------------+ mysql> update name set extra=json_insert(extra,'$.weight','abf') where id=1; Query OK, 0 rows affected (0.33 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> select * from name; +----+------------------------------------------------+ | Id | extra | +----+------------------------------------------------+ | 1 | {"old": 12, "name": "xiaoyu", "weight": "abf"} | +----+------------------------------------------------+ 1 row in set (0.28 sec) ----------------------------------------------------------------------------------- 也可以批量set ----------------------------------------------------------------------------------- mysql> update name set extra=json_set(extra,'$.cont',17,'$.class',3) where id=1; Query OK, 1 row affected (0.57 sec) Rows matched: 1 Changed: 1 Warnings: 0注意:
如果修改的key带数字的话，必须加双引号,否则sql报错mysql> update name set extra=json_set(extra,'$. "010A"',17) where id=1; Query OK, 1 row affected (0.57 sec) Rows matched: 1 Changed: 1 Warnings: 0
json_contains(json_doc, val [,path])和json_contains_path(json_doc,one_or_all,path[,path,path.....])
- json_contains()查看json中
最末端是否包含指定值，存在返回1，不存在返回0，路径不存在返回null。
第二个参数,'value'表示value为整型，'"value"'表示value为字符串类型，第二个参数最外层必须使用''。
最末端的意思是:只能查看json中最末端的value值，value值可以是一个整形，可以是一个字符串，也可以是一个数组1.当extra的json为
["769TS000019", "323"]mysql查询如下
mysql> select json_contains(extra,'"323"') from name where id =3; +------------------------------+ | json_contains(extra,'"323"') | +------------------------------+ | 1 | +------------------------------+ 1 row in set (0.28 sec) mysql> select json_contains(extra,'323') from name where id =3; +----------------------------+ | json_contains(extra,'323') | +----------------------------+ | 0 | +----------------------------+总结：1.json格式为[]时，json_contains()可省略path参数。2.323在json中看到是字符串类型，所以查询的时候必须是
json_contains(extra,'"323"')。
- json以最上面的为准
mysql> select json_contains(extra,'1605754200','$.batch_end_time') from name where id=2; +------------------------------------------------------+ | json_contains(extra,'1605754200','$.batch_end_time') | +------------------------------------------------------+ | 1 | +------------------------------------------------------+可以看出batch_end_time字段值为整形，所以
json_contains(extra,'1605754200','$.batch_end_time'),第二个参数为''即可；这种json结构，必须指定path。mysql> select >json_contains(extra,'40028482','$.group_pattern_explain.ori_loginid_list') from name where id=2; +----------------------------------------------------------------------------+ | json_contains(extra,'40028482','$.group_pattern_explain.ori_loginid_list') | +----------------------------------------------------------------------------+ | 1 | +----------------------------------------------------------------------------+可以看出ori_loginid_list的值为
数组的int型,也就是说明了value值为数组也是可以的。
- json_contains_path()确认json中是否包含指定路径。
存在则返回1，否则返回0，路径不存在返回null。one_or_all只能取值"one"或"all"，one表示只要有一个路径存在即可；all表示所有的路径都存在才行。
json以最上面的为例：mysql> select >json_contains_path(extra,'one','$.group_pattern_explain.aoi_list') from name >where id=2; +--------------------------------------------------------------------+ | json_contains_path(extra,'one','$.group_pattern_explain.aoi_list') | +--------------------------------------------------------------------+ | 1 | +--------------------------------------------------------------------+ 通常在where条件中使用,比如查询有该条路径的所有数据 mysql> select id from name where >json_contains_path(extra,'one','$.group_pattern_explain.aoi_list')>0; +----+ | id | +----+ | 2 | +----+
json_length()：并不是指json的
字符长度，而是指元素个数mysql> select json_length(extra) from name where id=2; +--------------------+ | json_length(extra) | +--------------------+ | 10 | +--------------------+ 1 row in set (0.33 sec)可以看出json_length(extra)表示extra的有10个元素，分别是type、task_id、group_id、vehicle_type、is_group_main、batch_end_time、batch_start_time、merge_from_loginid、group_pattern_explain、keys_group_pattern_explain
mysql> select json_length(extra->>'$.group_pattern_explain') from name where id=2; +------------------------------------------------+ | json_length(extra->>'$.group_pattern_explain') | +------------------------------------------------+ | 5 | +------------------------------------------------+表示extra->>'$.group_pattern_explain'下有5个元素，为aoi_list、loginid_list、pattern_info、ori_loginid_list、group_main_loginid
mysql> select json_length(extra->>'$.group_id') from name where id=2; +-----------------------------------+ | json_length(extra->>'$.group_id') | +-----------------------------------+ | 1 |也可用这种方式来判断字段是否有值，返回1表示有值，返回0表示为空
拼接函数
concat()
函数用于将多个字符串连接成一个字符串
mysql> SELECT CONCAT(’My’, ‘S’, ‘QL’);
-> ‘MySQL’
网友评论