json_contains:判断是否存在,具体不太懂,哈哈哈
json_extract:取json中的值;
mysql 解析 json字符串
表名:table_name,
字段名:json_object,
json_object对应的内容:{"msg":"success","code":0,"obj":{"username":"zhangsan",age:16}}
若是查询第一层,如msg,则
select json_extract(json_object ,'$.msg')) as msg from table_name;
若是查询第二层,则
select json_extract(json_extract(json_object ,'$.obj'),'$.username') as username from table_name;
若是按json内容做条件查询,则
select * from table_name where json_extract(json_object ,'$.msg') = 'success';
格式:json_extract(字段名,'$.key名')
以此类推,json数组类似下面的写法
select * from table_name where json_extract(json_object ,'$[0].msg}') = 'success';