如为8.0以上版本,可以直接使用原生的json解析器处理,低版本不支持
select t1.* ,concat(replace(substr(result,instr(result,‘"url":"‘) +7,instr(result,‘"}‘)-instr(result,‘"url":"‘) -7),‘https://‘,‘http://‘),‘@1500h‘) as temp_image -- json数据截取from ( SELECT t.* ,concat(substring_index(substring_index(t.image_file,‘"},{"‘, b.help_topic_id + 1), ‘"},{"‘, -1),‘"}‘) as result -- 列转行 FROM ( SELECT a.id ,a.image_file -- json array string FROM table_a a where length(a.image_file) > 3 limit 10,1 ) t join ( select 0 as help_topic_id union all select 1 as help_topic_id union all select 2 as help_topic_id union all select 3 as help_topic_id union all select 4 as help_topic_id union all select 5 as help_topic_id union all select 6 as help_topic_id union all select 7 as help_topic_id union all select 8 as help_topic_id union all select 9 as help_topic_id ) b ON b.help_topic_id < ((LENGTH(t.image_file) - LENGTH(REPLACE(t.image_file, ‘"},{"‘, ‘‘)))/5 + 1) order by result) t1;