vertica解析并提取json字段值

json字符串的内容如下:

[{"stockName":"阳光照明","stockProfit":"5500.0000","stockCode":"600261"},{"stockName":"京 运 通","stockProfit":"6664.5000","stockCode":"601908"}]

如果需要提取出json里的前3个stockName,可以通过regexp_substr函数实现。如下:

select
  substr(regexp_substr(f1, '"stockName":"[\w|\s]+', 1, 1), 14) as stockName1,
  substr(regexp_substr(f1, '"stockName":"[\w|\s]+', 1, 2), 14) as stockName2,
  substr(regexp_substr(f1, '"stockName":"[\w|\s]+', 1, 3), 14) as stockName3

如果要提取所有的字段,测试如下:

with temp as(
    select '[{"stockName":"阳光照明","stockProfit":"5500.1","stockCode":"600261"},{"stockName":"京 运 通","stockProfit":"6664.5000","stockCode":"601908"}]' as result
)
select 
substr(regexp_substr(result, '"stockName":"[\w|\s]+', 1, 1), 14) as stockName,
substr(regexp_substr(result, '"stockProfit":"[\w|\s|\.]+', 1, 1), 16) as stockProfit,
substr(regexp_substr(result, '"stockCode":"[\w|\s]+', 1, 1), 14) as stockCode
from temp
union all
select 
substr(regexp_substr(result, '"stockName":"[\w|\s]+', 1, 2), 14) as stockName,
substr(regexp_substr(result, '"stockProfit":"[\w|\s|\.]+', 1, 2), 16) as stockProfit,
substr(regexp_substr(result, '"stockCode":"[\w|\s]+', 1, 2), 14) as stockCode
from temp

语法:

REGEXP_SUBSTR( string, pattern [, position [,  occurrence  [, regexp_modifier...  [, captured_subexp ] ] ] ])

其中,参数occurrence非常关键,当正则表达式匹配出多个子字符串时,occurrence参数表示返回第几个子字符串。

 


转载于:

https://www.cnblogs.com/lavezhang/p/12191852.html

©️2020 CSDN 皮肤主题: 精致技术 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值