<em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011
2021-06-01 09:32:01
最近碰到個需求,源資料存在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子串做擷取,獲取兩列資料值。
這裡主要利用行號和分隔符來組合完成拆分的功能。
參考如下樣例。
主要利用連續數值作為索引(起始值為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
基於第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"')
附上完整指令碼樣例,全程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)
經過在個人普通設定PC實測,效能有點堪憂,耗時:資料量 約為15mins:50W ,不太能接受。有興趣或者經歷過的夥伴,出手來協助, 怎麼提高效率,或者來個新方案?
到此這篇關於SQLServer高效解析JSON格式資料的文章就介紹到這了,更多相關SQLServer解析JSON資料內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com!
相關文章
<em>Mac</em>Book项目 2009年学校开始实施<em>Mac</em>Book项目,所有师生配备一本<em>Mac</em>Book,并同步更新了校园无线网络。学校每周进行电脑技术更新,每月发送技术支持资料,极大改变了教学及学习方式。因此2011
2021-06-01 09:32:01
综合看Anker超能充系列的性价比很高,并且与不仅和iPhone12/苹果<em>Mac</em>Book很配,而且适合多设备充电需求的日常使用或差旅场景,不管是安卓还是Switch同样也能用得上它,希望这次分享能给准备购入充电器的小伙伴们有所
2021-06-01 09:31:42
除了L4WUDU与吴亦凡已经多次共事,成为了明面上的厂牌成员,吴亦凡还曾带领20XXCLUB全队参加2020年的一场音乐节,这也是20XXCLUB首次全员合照,王嗣尧Turbo、陈彦希Regi、<em>Mac</em> Ova Seas、林渝植等人全部出场。然而让
2021-06-01 09:31:34
目前应用IPFS的机构:1 谷歌<em>浏览器</em>支持IPFS分布式协议 2 万维网 (历史档案博物馆)数据库 3 火狐<em>浏览器</em>支持 IPFS分布式协议 4 EOS 等数字货币数据存储 5 美国国会图书馆,历史资料永久保存在 IPFS 6 加
2021-06-01 09:31:24
开拓者的车机是兼容苹果和<em>安卓</em>,虽然我不怎么用,但确实兼顾了我家人的很多需求:副驾的门板还配有解锁开关,有的时候老婆开车,下车的时候偶尔会忘记解锁,我在副驾驶可以自己开门:第二排设计很好,不仅配置了一个很大的
2021-06-01 09:30:48
不仅是<em>安卓</em>手机,苹果手机的降价力度也是前所未有了,iPhone12也“跳水价”了,发布价是6799元,如今已经跌至5308元,降价幅度超过1400元,最新定价确认了。iPhone12是苹果首款5G手机,同时也是全球首款5nm芯片的智能机,它
2021-06-01 09:30:45