做法一：

-- 创建临时表
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