mysql 截取字符串sql
SELECT substring_index(upload_pic_file,'/',-1) from erp_reject_loss_approval
mysql 解析json
json字符串解析
MySQL解析JSON字符串,可以使用MySQL的JSON函数。
使用JSON_EXTRACT
函数来提取JSON字符串中的特定值。例如:
SELECT JSON_EXTRACT('{"name": "Tom", "age": 18}', '$.name');
数组字符串解析
同样我们还是使用JSON_EXTRACT
函数,解析如下数据
SELECT JSON_EXTRACT(JSON_EXTRACT('[1,[2],3]', '$[1]'), '$[*]') AS result;
JSON数组字符串解析
基于以上我们对,json字符串和数组的解析,以此我们发挥一下使用JSON_EXTRACT
进行JSON数组字符串解析,我们试着解析下边的JSON数组字符串:
SELECT JSON_EXTRACT('[{"ruleCode": "active_default", "ruleName": "默认激活规则", "necessary": 0, "ruleValue": {"level": "app", "timesLimit": 1}, "activateType": "active", "triggerEvent": ["boot", "register", "login"], "ruleDescription": "基础激活规则, 当用户启动app,注册,登录的时候触发检查回传条件,回传次数1次, 规则级别 app", "openConfiguration": 0}, {"ruleCode": "active_pay_in_active_day", "ruleName": "归因当天下单上报规则", "necessary": 0, "ruleValue": {"level": "app", "funnel": "4/1", "timesLimit": 1, "afterActiveTime": 0, "afterActiveTimeUnit": "day"}, "activateType": "active_pay", "triggerEvent": ["recharge", "create_vip"], "ruleDescription": "自然日上报首单规则, 当用户发生订单支付的时候触发检查回传条件, 用户必须是在激活当天产生的订单才上报,并且仅上报1次", "openConfiguration": 0}]','$[1].ruleValue.funnel');
SELECT JSON_EXTRACT(upload_pic_file, '$[0].name') FROM erp_reject_loss_approval;