MSSQL 如何針對JSON 格式的儲存內容中的數值做計算? (Cross apply OpenJson)

2019-10-16

785
0
MSSQL
2019-10-17

摘要：隨手紀錄一下寫過的語法，在工作上我遇到了需要將30、 40 張表格的資料儲存在同一格Table裡，每個表格的內容又有所不同，我總不可能因此在一個Table建立上百個欄位，那樣實在太累啦~~，因此我決定將每張表格的資料皆轉換成JSON 格式，此時主管們就會說我將這些資料儲存成Json格式，那倘若我未來要統計圖表時，又必須將資料帶出來，在經過一次Json格式內容的轉換帶出需要的數值在進行統計，這樣不是很累嗎?

因此今天要介紹的是如何在資料帶出來之前，就以SQL語法將JSON中的需要的數值進行統計，此語法為 Cross apply OpenJson 。

此語法只支援SQL Server 2016以上的版本(兼容性等級130up)，所以在使用之前請先評估環境。

語法：

Cross apply OpenJson

     	With NewCte as  (Select J.[key] as JKey ,J.[value] as JVal  from [資料表名稱]
                    	Cross Apply OPENJSON ([資料表Json欄位]) as J	 　
                        )
		Select * from NewCte

範例 : 就拿目前正在做的程式來做個紀錄，首先我的資料表名稱為FinalNorm，我要針對資料表地內儲存著JSON格式資料的NormCont_Json欄位進行動作，在這之前我已經把Json 的Key值儲存在CaculateJsonField欄位，因此帶出來的Json資料>經過項目欄位的篩選之後，即可在最後一段的SQL 式進行SUM()加總的動作。

With NewCte as  (Select J.[key] as JKey,DB.NormClass_ID ,J.[value] as JVal,DB.Vendor_ID, CS.CaculateJsonField  from FinalNorm as DB 　
  INNER Join BasicNormClass as CS on DB.NormClass_ID = CS.C_ID 　
  Cross Apply OPENJSON (DB.NormCont_Json) as J	 　
  Where (1=1) 　
  AND DB.NormClass_ID  IN (Select C_ID  from BasicNormClass as DB2 Where  IsCaculate = 'Y') 　
  ), 　
  --篩選Json計算項目欄位
   NewCte2 as (	 Select DB.JKey,DB.NormClass_ID,JVal,DB.CaculateJsonField,DB.Vendor_ID 　
   from NewCte as DB  　
    Where DB.JKey = DB.CaculateJsonField COLLATE Chinese_Taiwan_Stroke_CI_AS  　
    ) 　
  --加總計算欄位
   Select JKey,NormClass_ID as CS_ID,Vendor_ID as InfoID ,sum(cast(JVal as int)) as SumVal  from NewCte2 　
   group by JKey,NormClass_ID ,Vendor_ID

參考文章連結:https://stackoverflow.com/questions/52023114/sum-json-objects-in-sql

回首頁