MySQL使用json_extract 函数过滤满足条件的json串

 字段中存的字符串为json串，需要取出"is_split": "0"的行，

{
	"channel": "weibo",
	"entrance": "main",
	"order_id": "16770027",
	"result": {
		"fund_result": {
			"service_fee_rate": "12",
			"split_type": "baoxian",
			"is_face": 1,
			"period_unit": "002007002",
			"is_push_credit": "0",
			"overdue_limit": "0",
			"overdue_fine_limit": "1",
			"priod_unit": "MONTH",
			"is_revolving": 1,
			"fund_code": "JLXD",
			"is_report_credit": 0,
			"is_depositary": 0,
			"balance": "3000000",
			"fund_late_fee_rate": "0",
			"fund_loan_rate": "24",
			"main_total_rate": "24",
			"is_self": 1,
			"qy_balance": "",
			"loan_time": "[00:00,22:00]",
			"overdue_fine_version": "1.0",
			"backup_fund_count": 1,
			"is_query_credit": 0,
			"is_bankacc": 0,
			"age_limit": "[18，60]",
			"is_split": "0",
			"main_overdue_fine_rate": "0.1",
			"insurance_rate": "7.2",
			"white_overdue_fine_rate": "0",
			"is_on": 1,
			"fund_name": "江铃小贷",
			"service_interest_accrual_method": "002004005",
			"cooperation_model": "fund",
			"main_service_calculation_method": "002004005",
			"master": "wj",
			"is_coupon": 0,
			"product_period": 6,
			"unit": 100,
			"main_penalty_rate": "3",
			"white_penalty_rate": "0",
			"bank_list": "ICBC,CCB,BOC,ABC,CMB,CIB,CEB,SPDB,PAB,GDB,PSBC,CITIC,SHB,CBMC,HXB",
			"is_ocr_pic": "0",
			"fund_interest_accrual_method": "002004005",
			"overdue_fine_limit_rate": "36",
			"is_convert": 0,
			"is_relocated": "0"
		},
		"strategy_result": {
			"strategy_trail": "微博_主资金路由决策流:通过",
			"strategy_msg": "有备用-动态顺序规则集认证成功",
			"strategy_version": "83",
			"strategy_id": "2706",
			"strategy_name": "微博_主资金路由决策流"
		}
	},
	"scene": "weibo_cashloan",
	"source": "weibo",
	"type": "fund",
	"user_id": "31286006",
	"version": "1.2"
}

方法一 用like

SELECT
*
FROM
loan_info a
LEFT JOIN loan_product_fund_record b ON a.ID = b.LOAN_ID
LEFT JOIN third_credit_query_order c ON b.RISK_ID = c.ORDER_NO
WHERE
a.LENDING_TAG = 'jlxd'
AND a.LOAN_STATUS IN ('002001007', '002001005')
AND b.IS_SPLIT = 0
AND c.QUERY_RESULT like '%"is_split":"0"%';

方法二：json_extract

SELECT
*
FROM
loan_info a
LEFT JOIN loan_product_fund_record b ON a.ID = b.LOAN_ID
LEFT JOIN third_credit_query_order c ON b.RISK_ID = c.ORDER_NO
WHERE
a.LENDING_TAG = 'jlxd'
AND a.LOAN_STATUS IN ('002001007', '002001005')
AND b.IS_SPLIT = 0
AND json_extract (QUERY_RESULT, '$.result.fund_result.is_split') = '0'