SQLServer高效解析JSON格式數(shù)據(jù)的實(shí)例過程
1. 背景
最近碰到個(gè)需求，源數(shù)據(jù)存在posgtreSQL中，且為JSON格式。那如果在SQLServer中則 無法直接使用，需要先解析成表格行列結(jié)構(gòu)化存儲(chǔ)，再復(fù)用。
樣例數(shù)據(jù)如下
‘[{“key”:“2019-01-01”,“value”:“4500.0”},{“key”:“2019-01-02”,“value”:“4500.0”},{“key”:“2019-01-03”,“value”:“4500.0”},{“key”:“2019-01-04”,“value”:“4500.0”},{“key”:“2019-01-05”,“value”:“4500.0”},{“key”:“2019-01-06”,“value”:“4500.0”},{“key”:“2019-01-07”,“value”:“4500.0”},{“key”:“2019-01-08”,“value”:“4500.0”},{“key”:“2019-01-09”,“value”:“4500.0”},{“key”:“2019-01-10”,“value”:“4500.0”},{“key”:“2019-01-11”,“value”:“4500.0”},{“key”:“2019-01-12”,“value”:“4500.0”},{“key”:“2019-01-13”,“value”:“4500.0”},{“key”:“2019-01-14”,“value”:“4500.0”},{“key”:“2019-01-15”,“value”:“4500.0”},{“key”:“2019-01-16”,“value”:“4500.0”},{“key”:“2019-01-17”,“value”:“4500.0”},{“key”:“2019-01-18”,“value”:“4500.0”},{“key”:“2019-01-19”,“value”:“4500.0”},{“key”:“2019-01-20”,“value”:“4500.0”},{“key”:“2019-01-21”,“value”:“4500.0”},{“key”:“2019-01-22”,“value”:“4500.0”},{“key”:“2019-01-23”,“value”:“4500.0”},{“key”:“2019-01-24”,“value”:“4500.0”},{“key”:“2019-01-25”,“value”:“4500.0”},{“key”:“2019-01-26”,“value”:“4500.0”},{“key”:“2019-01-27”,“value”:“4500.0”},{“key”:“2019-01-28”,“value”:“4500.0”},{“key”:“2019-01-29”,“value”:“4500.0”},{“key”:“2019-01-30”,“value”:“4500.0”},{“key”:“2019-01-31”,“value”:“4500.0”}]’
研究了下方法，可以先將 JSON串 拆成獨(dú)立的 key-value對(duì)，再來對(duì)key-value子串做截取，獲取兩列數(shù)據(jù)值。
2. 拆串-拆分JSON串至key-value子串
這里主要利用行號(hào)和分隔符來組合完成拆分的功能。
參考如下樣例。
主要利用連續(xù)數(shù)值作為索引(起始值為1)，從源字符串每個(gè)位置截取長度為1(分隔符的長度)的字符，如果為分隔符，則為有效的、待處理的記錄。有點(diǎn)類似于生物DNA檢測(cè)中的鳥槍法，先廣撒網(wǎng)，再根據(jù)標(biāo)記識(shí)別、追蹤。
/*
* Date : 2020-07-01
* Author : 飛虹
* Sample : 拆分 指定分割符的字符串為單列多值
* Input : 字符串'jun,cong,haha'
* Output : 列，值為 'jun', 'cong', 'haha'
*/
declare @s nvarchar(500) = 'jun,cong,haha'
,@sep nvarchar(5) = ',';
with cte_Num as (
select 1 as n
union all
select n+1 n from cte_Num where n<100
)
select d.s, a.n
,n-len(replace(left(s, n), @sep, '')) + 1 as pos,
CHARINDEX(@sep, s+@sep, n),
substring(s, n, CHARINDEX(@sep, s+@sep, n)-n) as element
from (select @s as s) as d
join cte_Num a
on
n<=len(s) and
substring(@sep+s, n, 1) = @sep
3. 取值-創(chuàng)建函數(shù)截取key-value串的值
基于第2步的結(jié)果，可以將JSON長串拆分為 key-value字符串，如 “2020-01-01”:“98.99”。到這一步，就好辦了。既可以自己寫表值函數(shù)來返回結(jié)果，也可以直接通過substring來截取。這里開發(fā)一個(gè)表值函數(shù)，來進(jìn)行封裝。
/*
*******************************************************************************
* Date : 2020-07-01
* Author : 飛虹
* Note : 利用patindex正則匹配字符,在while中對(duì)字符進(jìn)行逐個(gè)匹配、替換為空。
* Function : getDateAmt
* Input : key-value字符串，如 "2020-01-01":"98.99"
* Output : Table類型(日期列,數(shù)值列)。值為 2020-01-01, 98.99
*******************************************************************************
*/
CREATE FUNCTION dbo.getDateAmt(@S VARCHAR(100))
RETURNS @tb_rs table(dt date, amt decimal(28,14))
AS
BEGIN
WHILE PATINDEX('%[^0-9,-.]%',@S) > 0
BEGIN
-- 匹配：去除非數(shù)字 、頓號(hào)、橫線 的字符
set @s=stuff(@s,patindex('%[^0-9,-.]%',@s),1,'')
END
insert into @tb_rs
select SUBSTRING(@s,1,charindex(',',@s)-1)
, substring(@s,charindex(',',@s)+1, len(@s) )
return
END
GO
--測(cè)試
select * from DBO.getDateAmt('{"key":"2019-01-01","value":"4500.0"')
4. 完整樣例
附上完整腳本樣例，全程CTE，直接查詢，預(yù)覽效果。
;with cte_t1 as (
select * from
( values('jun','[{"key":"2019-01-01","value":"4500.0"},{"key":"2019-01-02","value":"4500.0"},{"key":"2019-01-03","value":"4500.0"},{"key":"2019-01-04","value":"4500.0"},{"key":"2019-01-05","value":"4500.0"},{"key":"2019-01-06","value":"4500.0"},{"key":"2019-01-07","value":"4500.0"},{"key":"2019-01-08","value":"4500.0"},{"key":"2019-01-09","value":"4500.0"},{"key":"2019-01-10","value":"4500.0"},{"key":"2019-01-11","value":"4500.0"},{"key":"2019-01-12","value":"4500.0"},{"key":"2019-01-13","value":"4500.0"},{"key":"2019-01-14","value":"4500.0"},{"key":"2019-01-15","value":"4500.0"},{"key":"2019-01-16","value":"4500.0"},{"key":"2019-01-17","value":"4500.0"},{"key":"2019-01-18","value":"4500.0"},{"key":"2019-01-19","value":"4500.0"},{"key":"2019-01-20","value":"4500.0"},{"key":"2019-01-21","value":"4500.0"},{"key":"2019-01-22","value":"4500.0"},{"key":"2019-01-23","value":"4500.0"},{"key":"2019-01-24","value":"4500.0"},{"key":"2019-01-25","value":"4500.0"},{"key":"2019-01-26","value":"4500.0"},{"key":"2019-01-27","value":"4500.0"},{"key":"2019-01-28","value":"4500.0"},{"key":"2019-01-29","value":"4500.0"},{"key":"2019-01-30","value":"4500.0"},{"key":"2019-01-31","value":"4500.0"}]')
,('congc','[{"key":"2019-01-01","value":"347.82608695652175"},{"key":"2019-01-02","value":"347.82608695652175"},{"key":"2019-01-03","value":"347.82608695652175"},{"key":"2019-01-04","value":"347.82608695652175"},{"key":"2019-01-07","value":"347.82608695652175"},{"key":"2019-01-08","value":"347.82608695652175"},{"key":"2019-01-09","value":"347.82608695652175"},{"key":"2019-01-10","value":"347.82608695652175"},{"key":"2019-01-11","value":"347.82608695652175"},{"key":"2019-01-14","value":"347.82608695652175"},{"key":"2019-01-15","value":"347.82608695652175"},{"key":"2019-01-16","value":"347.82608695652175"},{"key":"2019-01-17","value":"347.82608695652175"},{"key":"2019-01-18","value":"347.82608695652175"},{"key":"2019-01-21","value":"347.82608695652175"},{"key":"2019-01-22","value":"347.82608695652175"},{"key":"2019-01-23","value":"347.82608695652175"},{"key":"2019-01-24","value":"347.82608695652175"},{"key":"2019-01-25","value":"347.82608695652175"},{"key":"2019-01-28","value":"347.82608695652175"},{"key":"2019-01-29","value":"347.82608695652175"},{"key":"2019-01-30","value":"347.82608695652175"},{"key":"2019-01-31","value":"347.82608695652175"}]')
) as t(name, jsonStr)
) , cte_rn as (
select 1 as rn
union all
select rn+1 from cte_rn where rn < 1000
)
, cte_splitJson as (
SELECT a.name
,replace(replace(a.jsonStr,'[',''),']','') as jsonStr
,substring(replace(replace(a.jsonStr,'[',''),']','')
, b1.rn
, charindex('},', replace(replace(a.jsonStr,'[',''),']','')+'},', b1.rn)-b1.rn ) as value_json
from cte_t1 a
cross join cte_rn b1
where substring('},'+replace(replace(a.jsonStr,'[',''),']',''), rn, 2) = '},'
)
select *
from cte_splitJson a
cross apply dbo.getDateAmt(a.value_json) as t1
-- 注意這里生成行號(hào)時(shí)， 需要設(shè)置默認(rèn)遞歸次數(shù)
option(maxrecursion 0)
5. 問題
經(jīng)過在個(gè)人普通配置PC實(shí)測(cè)，性能有點(diǎn)堪憂，耗時(shí)：數(shù)據(jù)量 約為15mins:50W ，不太能接受。有興趣或者經(jīng)歷過的伙伴，出手來協(xié)助, 怎么提高效率，或者來個(gè)新方案？
到此這篇關(guān)于SQLServer高效解析JSON格式數(shù)據(jù)的文章就介紹到這了,更多相關(guān)SQLServer解析JSON數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了分享Sql Server 存儲(chǔ)過程使用方法的相關(guān)資料,需要的朋友可以參考下2022-09-09
SQL語句練習(xí)實(shí)例之七 剔除不需要的記錄行
相信大家肯定經(jīng)常會(huì)把數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫中，但是可能會(huì)有些記錄行的所有列的數(shù)據(jù)是null，這為null的數(shù)據(jù)是我們不需要2011-10-10
SqlServer服務(wù)中利用觸發(fā)器對(duì)指定賬戶進(jìn)行登錄ip限制提升安全性操作
這篇文章主要介紹了SqlServer服務(wù)中利用觸發(fā)器對(duì)指定賬戶進(jìn)行登錄ip限制提升安全性,本文給大家講解的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2023-02-02
SQL學(xué)習(xí)筆記二 創(chuàng)建表、插入數(shù)據(jù)的語句
SQL學(xué)習(xí)筆記二 創(chuàng)建表、插入數(shù)據(jù)的語句，需要的朋友可以參考下。2011-08-08
本文就給大家介紹一下 Microsoft SQL Server 中的過濾索引功能，本文通過場(chǎng)景模擬分析給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友參考下吧2021-07-07
這篇文章主要介紹了SQL Function 自定義函數(shù)詳解,需要的朋友可以參考下2016-10-10
SQL Server2008數(shù)據(jù)庫導(dǎo)入導(dǎo)出兼容性處理方案
SQL Server 的高版本數(shù)據(jù)庫恢復(fù)到低版本則可能會(huì)有兼容性問題，下面為大家介紹的是如何解決此類問題2014-05-05
sqlserver中存儲(chǔ)過程的遞歸調(diào)用示例
這篇文章主要介紹了sqlserver中存儲(chǔ)過程的遞歸調(diào)用示例,本文直接給出示例代碼,需要的朋友可以參考下2015-07-07
使用SQL Server分區(qū)表功能提高數(shù)據(jù)庫的讀寫性能
一般來說一個(gè)系統(tǒng)最先出現(xiàn)瓶頸的點(diǎn)很可能是數(shù)據(jù)庫。比如我們的生產(chǎn)系統(tǒng)并發(fā)量很高在跑一段時(shí)間后，數(shù)據(jù)庫中某些表的數(shù)據(jù)量會(huì)越來越大。海量的數(shù)據(jù)會(huì)嚴(yán)重影響數(shù)據(jù)庫的讀寫性能2023-05-05
sql語句優(yōu)化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的語句
sql語句優(yōu)化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的語句...2007-08-08