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