mysql 对json字段操作

mysql中保存json数据：

{
    "name": "张三"，
    "age": 18,
    "address": {
        "province": "河南"，
        "city": "郑州"
    }，
    "hobby": ["唱歌"，"跳舞"，"打篮球"]
}

新增sql：

insert into tablename (name,other_info) values('test','{"name": "张三","age": 18,"address": {"province": "河南","city": "郑州"},"hobby": ["唱歌","跳舞","打篮球"]}') 

查询：

--返回结果 '张三' 相当于->
select json_extract(other_info,'$.name') from tablename mp where id =1;
--返回结果 张三 相当于->> 会把结果中''去掉
select json_unquote(json_extract(other_info,'$.name')) from tablename mp where id =1;
--返回结果 '张三' 相当于json_extract
select other_info->'$.name' from tablename mp where id =1;
--返回结果 张三 相当于json_unquote
select other_info->>'$.name' from tablename mp where id =1;
--返回结果 郑州 查询对象中内容
select other_info ->>'$.address.city' from tablename where id =1;
--返回结果 打篮球 查询数组中内容
select other_info ->>'$.hobby[2]' from tablename where id =1;

json_search:

select * from tablename where json_search(other_info->'$.hobby','one','唱歌')

json_contains 查询数组内容：

select *  from tablename where json_contains(other_info->'$.hobby',json_array('唱歌'))

json_contains 查询对象：

select *  from (select other_info->'$.address' as address from tablename mp) t where json_contains(address,json_object('city','郑州'))

json_set 将数据插入JSON格式中，有key则替换，无key则新增：

update tablename set other_info = json_set(other_info,'$.gender','男') where id =1

json_insert 往json中插入新值，但不替换已经存在的旧值：

update tablename set other_info = json_insert(other_info,'$.gender','女') where id =1

json_replace 替换：

update tablename set other_info = json_replace(other_info,'$.gender','女') where id =1

json_remove 删除：

update tablename set other_info = json_remove(other_info,'$.gender') where id =1