mysql查询json字符串内容

参考文章：mysql json 基础查询_mysql json查询-CSDN博客 

1. mysql查询json字符串内容 （多层数组嵌套） 

select id,name
       JSON_EXTRACT(JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(config_json), '$.baseInfo')), '$.template_list[*].sms_content') sms_content,
       JSON_EXTRACT(JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(config_json), '$.baseInfo')), '$.template_list[*].sms_sign') sms_sign,
       JSON_EXTRACT(JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(config_json), '$.baseInfo')), '$.template_list[*].templateDTOS[*].smsTemplet') smsTemplate,
       JSON_EXTRACT(JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(config_json), '$.baseInfo')), '$.template_list[*].smsTemplateList[*].template_content') template_content,
       JSON_EXTRACT(JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(config_json), '$.baseInfo')), '$.template_list[*].smsTemplateList[*].template_sign') template_sign
from t_config_json config 
where config_json like '%template_list%'
order by create_time desc;

     2. mysql 中json提取查询

select id, name
       JSON_EXTRACT(JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(config_json), '$.baseInfo')), '$.sms_templet') smsTemplate,
       JSON_EXTRACT(JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(config_json), '$.baseInfo')), '$.sms_sign') smsSign,
       JSON_EXTRACT(JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(config_json), '$.baseInfo')), '$.templateDTOS[*].smsTemplet') dtoContent,
       JSON_EXTRACT(JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(config_json), '$.baseInfo')), '$.templateDTOS[*].smsTempletId') dtoTempletId,
       JSON_EXTRACT(JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(config_json), '$.baseInfo')), '$.smsTemplateList[*].template_content') listContent,
       JSON_EXTRACT(JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(config_json), '$.baseInfo')), '$.smsTemplateList[*].template_sign') listSign,
       JSON_EXTRACT(JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(config_json), '$.baseInfo')), '$.smsTemplateList[*].template_id') listTempletId,
			 JSON_EXTRACT(JSON_UNQUOTE(JSON_EXTRACT(JSON_UNQUOTE(config_json), '$.baseInfo')), '$.sms_rule_template_list[*].smsTemplateList') listTempletId
from t_config_json 
where operation_status=2
order by create_time desc;

3. mysql中转义字符like查询

json中原内容：

select * from t_config_json where id = 2580 and config_json like '%\\\\"TempletType\\\\":2%'