/*DROP TABLE IF EXISTS `sync_test2`;
CREATE TABLE `sync_test2` (
`id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`product_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '商品ID',
`test_json` json,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '服务商品服务价格' ROW_FORMAT = Dynamic;
INSERT INTO `sync_test2` VALUES (2, 'tttq', '{\"pin\": \"560103\", \"email\": \"abc@example.com\"}');
INSERT INTO `sync_test2` VALUES (3, '10011', '{\"pin\": \"10011\", \"email\": \"10011@example.com\"}');
INSERT INTO `sync_test2` VALUES (4, '10012', '{\"pin\": \"10012\", \"email\": \"10012@example.com\"}');
INSERT INTO `sync_test2` VALUES (5, '10013', '{\"pin\": \"10013\", \"email\": \"10013@example.com\"}');
INSERT INTO `sync_test2` VALUES (6, '10014', '{\"pin\": \"10014\", \"email\": \"10014@example.com\"}');
INSERT INTO `sync_test2` VALUES (7, '10015', '{\"pin\": \"10015\", \"email\": \"10015@example.com\"}');
INSERT INTO `sync_test2` VALUES (8, '10016', '{\"pin\": \"10016\", \"email\": \"10016@example.com\"}');
INSERT INTO `sync_test2` VALUES (9, '10016', '{\"pin\": \"10016\", \"user\":{\"name\": \"560103\"}, \"email\": \"10016@example.com\"}');
*/
/*
分类 函数 描述
创建json
json_array 创建json数组
json_object 创建json对象
json_quote 将json转成json字符串类型
查询json
json_contains 判断是否包含某个json值
json_contains_path 判断某个路径下是否包json值
json_extract 提取json值
column->path json_extract的简洁写法，MySQL 5.7.9开始支持
column->>path json_unquote(column -> path)的简洁写法
json_keys 提取json中的键值为json数组
json_search 按给定字符串关键字搜索json，返回匹配的路径
修改json
json_append 废弃，MySQL 5.7.9开始改名为json_array_append
json_array_append 末尾添加数组元素，如果原有值是数值或json对 象，则转成数组后，再添加元素
json_array_insert 插入数组元素
json_insert 插入值（插入新值，但不替换已经存在的旧值）
json_merge 合并json数组或对象
json_remove 删除json数据
json_replace 替换值（只替换已经存在的旧值）
json_set 设置值（替换旧值，并插入不存在的新值）
json_unquote 去除json字符串的引号，将值转成string类型
返回json属性
json_depth 返回json文档的最大深度
json_length 返回json文档的长度
json_type 返回json值得类型
json_valid 判断是否为合法json文档
*/
#根据条件查询
select * from sync_test2 where test_json ->> '$.user.name' = 'wcj';
select * from sync_test2 where test_json ->> '$.email' like '10011%';
select product_id,test_json ->> '$.pin' pin from sync_test2 where test_json ->> '$.email' like '10011%';
select id,product_id,json_pretty(test_json ->> '$.user')pin from sync_test2 where test_json ->> '$.pin' = '10016';
#查询json中值存不存在
select json_contains(test_json ->> '$.pin',"560103") from sync_test2;
#查询json中key存不存在
select json_contains_path(test_json, 'one', "$.user.name") from sync_test2;
select json_contains_path(test_json, 'all', "$.user", "$.email") from sync_test2;
#查询json中所有key
select json_keys(test_json) from sync_test2 where test_json ->> '$.user.name' = 'wcj';
#值变更
#替换现有值并添加不存在的值
update sync_test2 set test_json = json_set(test_json, "$.user.name", "wcj") where id = 9;
#插入值，但不替换现有值
update sync_test2 set test_json = json_insert(test_json, "$.user.age", 30) where id = 9;
#仅替换现有值
update sync_test2 set test_json = json_replace(test_json, "$.user.age", 40, "$.user.grade", 100) where id = 9;
#能从JSON文档中删除数据
update sync_test2 set test_json = json_remove(test_json, "$.user.age") where id = 9;
#===================JSON基础工具======================
#使用JSON_ARRAY方法定义JSON数组；
SELECT JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME())
#结果：[1, "abc", null, true, "11:30:24.000000"]
#JSON_OBJECT 方法定义JSON对象
SELECT JSON_OBJECT('id', 87, 'name', 'carrot')
#结果{"id": 87, "name": "carrot"}
#JSON_QUOTE 将JSON对象转义成String, 就是将内部的符 号进行转义，并整体包裹上双引号；
SELECT JSON_QUOTE(' "null" ')
#结果 "\"null\""
#将JSON内容美化并输出；
select JSON_PRETTY(test_json) from sync_test2 where id = 9;
#类似 ->的用法不同的是，返回的是里面值的数组
select JSON_EXTRACT(test_json, '$.user.*') from sync_test2 where id = 9;
select JSON_EXTRACT(test_json, '$[1 to 2]') from sync_test2 where id = 10;
# 获取数据一级目录的值
SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*');
#[1, 2, [3, 4, 5]]
#获取二级目录里面所有的值
SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.c[*]')
#[3, 4, 5]
# 获取数据中所有key为b的值
SELECT JSON_EXTRACT('{"a": {"b": 1}, "c": {"b": 2},"b":9}', '$**.b');
#[1, 2]
#获取数组中1-3位中的值
SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');
#[2, 3, 4]
#把json数据转成表
SELECT * FROM JSON_TABLE('[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]'
,"$[*]" COLUMNS(rowid FOR ORDINALITY
,ac VARCHAR(100) PATH "$.a" DEFAULT '111' ON EMPTY DEFAULT '999' ON ERROR
,aj JSON PATH "$.a" DEFAULT '{"x": 333}' ON EMPTY
,bx INT EXISTS PATH "$.b")
) AS tt;
-- 追加成数组，IFNULL(json_test,'{}' )为字段null赋值“{}”值
update t_user set json_test = JSON_MERGE(IFNULL(json_test,'{}' ),'{"name":"test1"}') where id = 1;
-- json字段设置值
update t_user set json_test = JSON_SET(IFNULL(json_test,'{}'),'$.name','test11','$.type',2) where id = 1;
--移除json字段中keyd的值
update t_user set json_test = json_remove(json_test,'$.name','$.type') where id = 1;
#####{"orderNumberList": ["BHZ01230512000058", "BHZ01230512000043", "BHZ01230504000041", "BHZ01230504000049"]}
# 判断对象中是否存在值，存在追加，不存在添加
update sync_storehouse_receipt set expound_json =
CASE WHEN expound_json IS NULL THEN JSON_MERGE('{}' ,'{"orderNumberList": ["BHZ01230512000000"]}')
WhEN json_contains_path(expound_json,'one','$.orderNumberList')=0 then JSON_MERGE_PATCH(expound_json , '{"orderNumberList": ["BHZ01230512000058"]}')
#WHEN json_contains_path(expound_json,'one','$.orderNumberList')=1 then JSON_ARRAY_APPEND(expound_json, '$.orderNumberList','BHZ01230504000049')
ELSE JSON_MERGE_PATCH(expound_json , '{"orderNumberList": ["BHZ01230504000041"]}') END
where put_storage_number = 'RK20210515000001';
#判断对象数组中是否存在某值，存在移除数组中某值
UPDATE sync_storehouse_receipt
SET expound_json = JSON_REMOVE(expound_json, JSON_UNQUOTE(JSON_SEARCH(expound_json, 'one', 'BHZ01230512000043',null,'$.orderNumberList')))
where put_storage_number = 'RK202305127626006' and JSON_SEARCH(expound_json, 'one', 'BHZ01230512000043',null,'$.orderNumberList') IS NOT NULL;
# {"1231": {"id": "1231", "time": "2023-06-09"}}格式数据查询
SELECT * FROM sync_storehouse_outbound_order_count WHERE JSON_OVERLAPS (outbound_order_list -> '$**.id',JSON_ARRAY ("1231"))
-- 删除这种格式数 {"1233": {"id": "1233", "time": "2023-06-09"}}
update sync_storehouse_outbound_order_count SET outbound_order_list = JSON_REMOVE(outbound_order_list, '$."1233"') WHERE id=1;
# ["1", "2", "3", "4", "5"]
SELECT * FROM sync_storehouse_outbound_order_count where JSON_CONTAINS(outbound_order_list, JSON_ARRAY('1'))
--删除数组中的数据
update sync_storehouse_outbound_order_count SET outbound_order_list = JSON_REMOVE(outbound_order_list, JSON_UNQUOTE(JSON_SEARCH(outbound_order_list, 'one', '5'))) WHERE id=4;
-- 数组中存在追加不存在执行
update sync_storehouse_outbound_order_count SET outbound_order_list = outbound_order_list =
case when !JSON_CONTAINS(outbound_order_list, JSON_QUOTE( '1' )) THEN JSON_ARRAY_APPEND( outbound_order_list, '$','1')
WHEN outbound_order_list IS NULL THEN outbound_order_list
else outbound_order_list
end ,
outbound_order_list = CASE WHEN JSON_LENGTH(outbound_order_list) > 1000 THEN JSON_REMOVE(outbound_order_list, '$[0]') ELSE outbound_order_list END ,
outbound_order_list = CASE WHEN outbound_order_list IS NULL THEN JSON_ARRAY('1') WHEN !JSON_CONTAINS(outbound_order_list, JSON_QUOTE('1')) THEN JSON_ARRAY_APPEND(outbound_order_list, '$', '1') ELSE outbound_order_list END,
, update_at = now() ; WHERE id=4;
==========================================================
select JSON_MERGE_PATCH(
'{"sku1":{"sku":"sku1","update_time":11111},"sku2":{"sku":"sku2","update_time":11111},"sku3":{"sku":"sku3","update_time":2222,"execute_time":2222}}' #目标字段
,
CONCAT('{"',(
SELECT
GROUP_CONCAT(sku SEPARATOR '":null,"') as sku
FROM
JSON_TABLE(
JSON_MERGE_PATCH(
'{"sku1":{"sku":"sku1","update_time":11111},"sku2":{"sku":"sku2","update_time":11111},"sku3":{"sku":"sku3","update_time":2222,"execute_time":2222}}' #目标字段
,
'{"sku1":{"sku":"sku1","execute_time":11111}}'
),
'$.*'
COLUMNS (
sku CHAR(20) PATH '$.sku',
update_time INT PATH '$.update_time',
execute_time INT PATH '$.execute_time'
)
) AS t where update_time=execute_time #json的过滤条件
)
,'":null}') #拼接出移除的json
)
#一条sql语句移除多个符合条件的key，优化掉无用查询和括号。
12-14
08-25
1万+
1万+
03-30
821
821
“相关推荐”对你有帮助么？
-
非常没帮助 -
没帮助 -
一般 -
有帮助 -
非常有帮助
提交
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
