做法一:
-- 创建临时表
with t as (
select '[{"carrer":"大数据开发工程师","dream":["开个便利店","去外面逛一逛","看本好书"]},{"carrer":"退休农民","dream":["儿子听话","带孙子"]}]' as dfs
)
select get_json_object(dfs,'$.\[0].carrer') as carrer0
, get_json_object(dfs,'$\[1].carrer') as carrer1
, get_json_object(dfs,'$\[0].dream[0]') as dream0
, get_json_object(dfs,'$\[1].dream[0]') as dream1
from t limit 222
参考:https://www.cnblogs.com/LIAOBO/p/16242061.html