select id,name,json_unquote(json_extract(json_column,'$[',idx,']','.param1')) as param1,

json_unquote(json_extract(json_column,'$[',idx,']','.param2')) as param2

from test1

join 

(select 0 as idx union

select 1 as idx union

select 2 as idx union

select 3 as idx union

select 4 as idx union

select 5 as idx union

select 6 as idx union

select 7 as idx union

select 8

) as  test2

where json_extract(json_column,concat('$[',idx,']')) is not null ;

 

1.json_unquote:去掉json字符串中的双引号，

2.构造 test2，可以方便json_extract取值，思路很妙，Mark一下