json查询

# 查询json中字段
SELECT data->'$.name' AS name, data->'$.age' AS age FROM my_table;

# 查询条件 
SELECT * from work_target_w where JSON_EXTRACT(dts,'$.m')='02'
#简单写法
SELECT * from work_target_w where dts->'$.m'='02'
SELECT data FROM my_table WHERE data->'$.age' > 25;
# like用法
SELECT * FROM work_target_w WHERE dts->'$.m' like '%2%'

#JSON_CONTAINS和JSON_OBJECT
 SELECT * FROM work_target_w WHERE JSON_CONTAINS(dts, JSON_OBJECT('m', '02'))
多个属性 
SELECT * FROM work_target_w WHERE JSON_CONTAINS(dts, JSON_OBJECT('m', '02','name', 'val2'))

json查询 [,[]]

# 查询json数组
 SELECT * FROM work_target_d WHERE JSON_CONTAINS(persons, JSON_OBJECT('id', 'R6M8QT'))

$[*] 代表筛选所有的数组对象，只要所有对象中有一个name模糊查询符合就得到结果 
$[0]写成数字就表示数组中下标为0的对象name进行模糊查询匹配 

select * from works where persons->'$[0].name' like '%张三%'

插入

INSERT INTO my_table (id, data) VALUES (1, '{"name": "Alice", "age": 25}');

更新 JSON

UPDATE my_table SET data = JSON_SET(data, '$.age', 26) WHERE id = 1;

删除 JSON

UPDATE my_table SET data = JSON_REMOVE(data, '$.name') WHERE id = 1;