ODPS SQL 拆解json字段

1.切分包含数组和json字符串

#1.原始info字段的数据:
[
{"num":1,"name":"Lisa","score":90},
{"num":2,"name":"Melody","score":80}
]

#2.解析代码:
select distinct 
  b.id,
  b.info,
  GET_JSON_OBJECT(b.info,'$.id') as id,
  GET_JSON_OBJECT(b.info,'$.name') as name,
  GET_JSON_OBJECT(b.info,'$.score') as score
from(
  select 
    TRANS_ARRAY(1,'|',a.id,a.info) as (id,info)
  from(
    select distinct
        id, 
        regexp_replace(regexp_replace(regexp_replace(info,'^\\[',''),'\\]$',''),'},\\{','}|{') as info
    from tmp
  ) a
) b
;

#3.详细描述:
#I:先把以[开头和以]结尾的符号替换为空，再把多个json字符串中间的逗号},{替换成竖杠}|{，方便后面切分；
#II:再用TRANS_ARRAY把一行数据根据切分符号，切成多行；
#III:最后用GET_JSON_OBJECT获取json内容；

未完待续……