Mysql的JSON格式操作

命令说明:

JSON_CONTAINS(json_doc, searchKey,path)检查是否包含,返回1表示包含,0不包含
JSON_SEARCH(json_doc,type,searchKey)返回searchKey对应的path路径,type有两个值,one返回第一个匹配的,all返回所有匹配到的.
JSON_EXTRACT(json_doc, path)：提取JSON文档中的数据。
JSON_SET(json_doc, path, val)：更新JSON文档中的数据。
JSON_INSERT(json_doc, path, val)：向JSON文档中插入数据，如果路径已存在，则不进行任何操作。
JSON_REPLACE(json_doc, path, val)：替换JSON文档中的数据。
JSON_REMOVE(json_doc, path)：从JSON文档中删除数据。
JSON_EXTRACT(json_doc,path) 从 JSON 文本中提取数据
JSON_UNQUOTE(json_doc): 去掉 JSON 数据中的引号
JSON_SET(json_doc,path,val): 更新
JSON_ARRAY(n,n1,n2...): 创建 JSON 数组
JSON_OBJECT(key1,val1,key2,val2...): 创建 JSON 数据

查询示例

# 查询文本中有支付宝的,返回路径path
# JSON_SEARCH(json, one_or_all, search_str, escape_char, path)
> select 
  json_search(policy_data,'one','支付宝') 
from agent_policy 
where instr(policy_data,'支付宝') >0
结果:
"$.shareProfit[4].title"
参数说明:
one_or_all：必需的。可用值：‘one’, ‘all’。
规则如下：‘one’：返回第一个匹配的路径。‘all’：返回所有匹配的路径。所有的路径会包装在一个数组内返回。
search_str：必需的。被搜索的字符串。 您可以在 search_str 参数中使用 % 和 _ 通配符，就像 LIKE 一样：% 匹配任意数量的任意字符。_ 匹配一个任意字符。
escape_char：可选的。 如果 search_str 中包含 % 和 _，需要在他们之前添加转移字符。默认是 \。
path：可选的。只能在此路径下进行搜索。

#查询数组下code为CREDIT的路径
select 
  json_search(template_data,'one','CREDIT',null,'$.shareProfit[*].code') 
from policy_temp



# 查询文本中有支付宝的,返回路径path,并去掉双引号
select 
  JSON_UNQUOTE(json_search(policy_data,'one','支付宝'))
from agent_policy 
where instr(policy_data,'支付宝') >0
结果:
$.shareProfit[4].title

#另一种写法
/*
  SELECT 
    attrs->'$.name' as name,    查询json数据中的name的值并赋值给name
    JSON_UNQUOTE(attrs->'$.name'),  
    attrs->>'$.name' ->>取出的结果不带双引号
 FROM a_goods
*/

SELECT * FROM a_goods WHERE attrs->>'$.age' = '20' 
//要特别注意的是，JSON 中的元素搜索是严格区分变量类型的，比如说整型和字符串是严格区分的,即 “20”和20

查询示例2

#根据路径查询内容
select 
  JSON_EXTRACT(policy_data,'$.reward') 
from agent_policy ap 
where agent_id =1738425214003621890
结果:
[{"key": 1702020413249, "code": "ACTIVATION", "title": "激活奖励", "isBelow": false, "defaultVal": 0}, {"key": 1702020416871, "code": "TRAFFIC", "title": "流量卡奖励", "isBelow": false, "defaultVal": 0}]

#使用'$.reward[0]'取出下标0的.
#使用'$.reward[m to n]'取下标m到n的所有范围
select 
  JSON_EXTRACT(policy_data,'$.reward[0]') 
from agent_policy ap 
where agent_id =1738425214003621890
结果:
{"key": 1702020413249, "code": "ACTIVATION", "title": "激活奖励", "isBelow": false, "defaultVal": 0}

select 
  JSON_EXTRACT(policy_data,'$.reward[0].title') 
from agent_policy ap 
where agent_id =1738425214003621890
结果:
"激活奖励"

#去掉双引号
select 
  JSON_UNQUOTE(policy_data -> '$.reward[0].title') 
from agent_policy ap 
where agent_id =1738425214003621890
结果:
激活奖励

新增示例

#设置属性
#json_set(json,path,val,path,val...)  新增并更新
select 
  json_set(policy_data , '$.reward[0].title','激活奖励新名字') 
from agent_policy ap where agent_id =1738425214003621890
结果是设置后这个字段的全部内容:
{"reward": [{"key": 1702020413249, "code": "ACTIVATION", "title": "激活奖励新名字", "isBelow": false, "defaultVal": 0}, {"key": 1702020416871, "code": "TRAFFIC", "title": "流量卡奖励", "isBelow": false, "defaultVal": 0}], "shareProfit": [{"key": 1702020433845, "code": "CREDIT", "stag": false, "title": "贷记卡", "fixedFee": "0", "brandRate": 0.55, "singleFee": "0", "defaultVal": 0.52, "singleLeave": "0", "fixedFeeFlag": false, "maxBrandRate": 0.6, "singleFeeFlag": true}, {"key": 1702020434020, "code": "DEBIT", "stag": false, "title": "借记卡", "fixedFee": "20", "brandRate": 0.5, "singleFee": "0", "defaultVal": 0.45, "singleLeave": "0", "fixedFeeFlag": true, "maxBrandRate": 0.6, "singleFeeFlag": false}, {"key": 1702020434170, "code": "QUICKPAY", "stag": false, "title": "快捷支付", "fixedFee": "0", "brandRate": 0.5, "singleFee": "0", "defaultVal": 0.45, "singleLeave": "0", "fixedFeeFlag": false, "maxBrandRate": 0.6, "singleFeeFlag": true}, {"key": 1702020434505, "code": "FLASH", "stag": false, "title": "手机Pay", "fixedFee": "0", "brandRate": 0.38, "singleFee": "0", "defaultVal": 0.3, "singleLeave": "0", "fixedFeeFlag": false, "maxBrandRate": 0.38, "singleFeeFlag": false}, {"key": 1702020434664, "code": "ALIPAY", "stag": false, "title": "支付宝大额", "fixedFee": "0", "brandRate": 0.55, "singleFee": "0", "defaultVal": 0.44, "singleLeave": "0", "fixedFeeFlag": false, "maxBrandRate": 0.6, "singleFeeFlag": false}, {"key": 1702020434829, "code": "WXPAY", "stag": false, "title": "微信", "fixedFee": "0", "brandRate": 0.38, "singleFee": "0", "defaultVal": 0.3, "singleLeave": "0", "fixedFeeFlag": false, "maxBrandRate": 0.38, "singleFeeFlag": false}, {"key": 1702020434994, "code": "UNIONPAY", "stag": false, "title": "银联云闪付", "fixedFee": "0", "brandRate": 0.38, "singleFee": "0", "defaultVal": 0.3, "singleLeave": "0", "fixedFeeFlag": false, "maxBrandRate": 0.38, "singleFeeFlag": false}, {"key": 1742607828268, "code": "ALIPAY_SMALL", "stag": false, "title": "支付宝小额", "brandRate": "0.38", "defaultVal": "0.38", "fixedFeeFlag": false, "maxBrandRate": "0.38", "singleFeeFlag": false}]}

#对象新增属性
json_insert(json,path,val,path,val...) 只新增不更新,已存在的属性不操作
#原来只有4个元素,现在添加第五个元素y
select json_insert(json,'$[4]','y')

#对象更新属性
json_replace(json,path,val,path,val...) 只更新不新增.
select json_replace(json,'$[0]','y')


#新增josn_arry_append(json,path,val,path,val....). 追加在最后
#在下标0中增加字符串元素x, 在下标3中增加字符串元素y
select josn_arry_append(json,'$[0]','x','$[3]','y')
#$.shareProfit是一个数组,给数组中添加字符串元素xx
select JSON_ARRAY_APPEND(template_data,'$.shareProfit','xx')
#$.shareProfit中添加一个json_obejct对象
JSON_ARRAY_APPEND(template_data,'$.shareProfit',JSON_OBJECT('code','ALIPAY_SMALL','title','支付宝小额'))

#新增josn_arry_insert(json,path,val,path,val...). 在指定位置新增,元素向后移动
#在下标6增加一个元素.
select JSON_ARRAY_APPEND(template_data,'$.shareProfit[6]','xx')

删除示例

#删除属性
#json_remove(json,path,path...)返回的是policy_data字段删除对应path后的全部内容
update 
agent_policy 
set policy_data= json_remove(policy_data,replace(JSON_UNQUOTE(json_search(policy_data,'one','支付宝小额')),'.title','')) 
where instr(policy_data,'支付宝小额') > 0

创建对象

#创建jsonObject对象
#JSON_OBJECT(key1,value1,key2,value2)
select 
 JSON_OBJECT('code','ALIPAY_SMALL','title','支付宝小额','brandRate','0.38')
from dual

#JSON_array(n,n1,n2,n3)
select 
 JSON_array('node1','node2','node3')
from dual

#字符串转json对象
#json转为jsonArray数组
select case('[1,2,"abc"]' as json) from dual
#json转为jsonObject对象
select case('{"key1":1,"key2":"abc"}' as json) from dual


select agent_id,json_extract(policy_data,"$.shareProfit[4].defaultVal"),json_extract(policy_data,"$.shareProfit[4].title") from agent_policy 

select policy_name,json_extract(template_data,"$.shareProfit[4].defaultVal"),json_extract(template_data,"$.shareProfit[4].title") from policy_temp

update policy_temp set template_data = JSON_ARRAY_APPEND(template_data,'$.shareProfit',JSON_OBJECT('code','ALIPAY_SMALL','title','支付宝小额','brandRate','0.38','maxBrandRate','0.38','defaultVal','0.26','singleFeeFlag',false,'fixedFeeFlag',false,'stag',false,'key',1742607828268)) 
where instr(template_data,'支付宝小额') =0

update agent_policy set policy_data= json_remove(policy_data,replace(JSON_UNQUOTE(json_search(policy_data,'one','支付宝小额')),'.title','')) 
where instr(policy_data,'支付宝小额') > 0

select json_set(policy_data , '$.reward[0].title','激活奖励新名字') from agent_policy ap where agent_id =1738425214003621890

select * from  policy_temp
where JSON_UNQUOTE(json_extract(template_data,replace(JSON_UNQUOTE(json_search(template_data,'one','ALIPAY')),'.code','.brandRate'))) < 0.51

select agent_policy_id,b.real_name,json_extract(policy_data,replace(JSON_UNQUOTE(json_search(policy_data,'one','ALIPAY')),'.code','.brandRate')) from agent_policy ap
left join agent_info b on ap.agent_id = b.agent_id
where JSON_UNQUOTE(json_extract(policy_data,replace(JSON_UNQUOTE(json_search(policy_data,'one','ALIPAY')),'.code','.brandRate'))) < 0.51

update policy_temp set template_data= json_set(template_data,replace(JSON_UNQUOTE(json_search(template_data,'one','ALIPAY')),'.code','.brandRate'),'0.51')
where JSON_UNQUOTE(json_extract(template_data,replace(JSON_UNQUOTE(json_search(template_data,'one','ALIPAY')),'.code','.brandRate'))) < 0.51

-- ,"stableRate": "0.505","stableRateFlag": "true"
select * from policy_temp pt 
where JSON_UNQUOTE(JSON_EXTRACT(template_data,JSON_UNQUOTE(json_search(template_data,'one','true',null,'$.shareProfit[*].stableRateFlag')))) = 'true'


update agent_policy set policy_data = json_set(policy_data,replace(JSON_UNQUOTE(json_search(policy_data,'one','CREDIT')),'.code','.stableRateFlag'),'true')

where JSON_UNQUOTE(JSON_EXTRACT(policy_data ,JSON_UNQUOTE(json_search(policy_data,'one','true',null,'$.shareProfit[*].stableRateFlag')))) is null
and agent_policy_id  != 1745626136286953474