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内容;
未完待续……