首頁 > 軟體

SQLServer高效解析JSON格式資料的範例過程

2022-08-29 14:04:38

1. 背景

最近碰到個需求,源資料存在posgtreSQL中,且為JSON格式。那如果在SQLServer中則 無法直接使用,需要先解析成表格行列結構化儲存,再複用。

樣例資料如下

‘[{“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串 拆成獨立的 key-value對,再來對key-value子串做擷取,獲取兩列資料值。

2. 拆串-拆分JSON串至key-value子串

這裡主要利用行號和分隔符來組合完成拆分的功能。
參考如下樣例。
主要利用連續數值作為索引(起始值為1),從源字串每個位置擷取長度為1(分隔符的長度)的字元,如果為分隔符,則為有效的、待處理的記錄。有點類似於生物DNA檢測中的鳥槍法,先廣撒網,再根據標記識別、追蹤。

/*
 * 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. 取值-建立函數擷取key-value串的值

基於第2步的結果,可以將JSON長串拆分為 key-value字串,如 “2020-01-01”:“98.99”。到這一步,就好辦了。既可以自己寫表值函數來返回結果,也可以直接通過substring來擷取。這裡開發一個表值函數,來進行封裝。

 /*
  *******************************************************************************
  *     Date : 2020-07-01
  *   Author : 飛虹
  *     Note : 利用patindex正則匹配字元,在while中對字元進行逐個匹配、替換為空。
  * Function : getDateAmt
  *   Input  : key-value字串,如 "2020-01-01":"98.99"
  *   Output : Table型別(日期列,數值列)。值為 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
			 -- 匹配:去除非數位 、頓號、橫線 的字元
 			 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
 
 --測試
 select  * from DBO.getDateAmt('{"key":"2019-01-01","value":"4500.0"')
 

4. 完整樣例

附上完整指令碼樣例,全程CTE,直接查詢,預覽效果。

;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 
	-- 注意這裡生成行號時, 需要設定預設遞迴次數
	option(maxrecursion 0)

5. 問題

經過在個人普通設定PC實測,效能有點堪憂,耗時:資料量 約為15mins:50W ,不太能接受。有興趣或者經歷過的夥伴,出手來協助, 怎麼提高效率,或者來個新方案?

到此這篇關於SQLServer高效解析JSON格式資料的文章就介紹到這了,更多相關SQLServer解析JSON資料內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com!


IT145.com E-mail:sddin#qq.com