有这样一个json串
{"viewdata":[{"city_id":"59","position_id":0,"qd_title":"网红打卡地","list_id":35},{"city_id":"...下面介绍两种方法
法一get_json_object+正则
1.首先可以使用get_json_object函数,提取出数组,但是这个返回的是一个字符串
select get_json_object('{"viewdata...":"网红打卡地","list_id":135},
{"city_id":"39","position_id":1,"qd_title":"看青山游绿水","list_id":327}]}',
'$.viewdata...刚刚得到的字符串}','(\\[|\\]|")','')
3.使用字符串分割函数split
select
split(
regexp_replace(
get_json_object('{"viewdata...list_id":135},
{"city_id":"39","position_id":1,"qd_title":"看青山游绿水","list_id":327}]}',
'$.viewdata