mysql 列转行 并进行json截取(mysql 5.6)

 

如为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;

 

相关文章