Mysql对存储JSON数据的查询和修改

 # 创建表 product 添加数据
insert into product (name,details) values ("华为",'{"cpu":"骁龙865","system":"android","storage":"256G"}')
insert into product (name,details) values ("华为",'{"cpu":"骁龙865","system":"android","storage":["256G","64G","128G"]}')

select *,JSON_EXTRACT(details,'$.cpu') as cpu from product where JSON_EXTRACT(details,'$.cpu') = "骁龙865"

# 查询C 的所有数据  [3,4,5]
select JSON_EXTRACT('{"a":1, "b":2,"c":[3,4,5]}','$.c[*]') ;  

// mysql 8 应该可以用 
# 查询 C 索引第一个到第二个
select JSON_EXTRACT('{"a":1, "b":2,"c":[3,4,5]}','$.c[1 to 2]') ;  

select JSON_EXTRACT('{"a":1, "b":2,"c":[3,4,5]}','$.c[last - 1]') ;  

select *,JSON_EXTRACT(details,'$.cpu') as cpu from product
# 简写方式
select details ->'$.cpu' from product

# JSON_UNQUOTE 去掉引号
select JSON_UNQUOTE(details ->'$.cpu') from product
# 简写方式
select details ->> '$.cpu' from product

# 查询 system 为 Android 的数据
# JSON_CONTAINS JSON数据包含
select * from product where JSON_CONTAINS(details,JSON_OBJECT('system','android'));

# 查询 details的 json 数据中是否有 cpu key值 可写多个
# one 表示 有一个就返回  all 表示 所有满足才返回
select * from product where JSON_CONTAINS_PATH(details,'one','$.cpu') 
select * from product where JSON_CONTAINS_PATH(details,'one','$.cpu','$.storage') 

# JSON_SEARCH 搜索
select details,JSON_SEARCH(details,'one','64G') from product

SELECT GROUP_CONCAT(name),price FROM product GROUP BY price;
SELECT JSON_ARRAYAGG(name),price FROM product GROUP BY price;

# 重新JSON数据的所有key
select JSON_KEYS(details) from product;

# 重新json数据有多少个key
select JSON_LENGTH(details) from product;

# 通过json_set函数,来修改data字段的值
update tab_json set data = json_set(data,"$.address","Shenzhen");