SQLServer中JSON文檔型數據的查詢問題解決

Posted on by

近日在項目中遇到一個問題： 如何在報表中統計JSON格式存儲的數據？

例如有個調查問卷記錄表，記錄每個問題的答案。 其結構示意如下（橫表設計）

Id user date Q1_Answer Q2_Answer Q3_Answer
行Id 答題用戶 答題日期 問題一結果 問題二結果 問題三結果

在[Q1_Answer]、[Q2_Answer]、[Q3_Answer]中記錄的數據格式是JSON文檔內容，因為是選項值，而且考慮到可能有多選， 所以存儲的格式如下：

1 [ 
     {"code":"a", "desc":"Jan."},
     {"code":"b", "desc":"Feb."}
  ]

其中 code 表示選項， desc 表示選項的文字描述。

現在，用戶想用PowerBI 來實現對結果的統計。有如下幾個問題：

  • 在Power BI中，無法直接從JSON數據中讀取到選項值
  • 如果是多選，又該如何處理。

比較適合分析的數據結構應該長這樣：

行Id 答題用戶 答題日期 問題編號 用戶選項 選項文字
1 user1 2021-6-26 Q1 A Jan.
2 user1 2021-6-26 Q2 A Mon.
3 user1 2021-6-26 Q2 B Tue.
4 user1 2021-6-26 Q3 A Swimming
6 user2 2021-6-26 Q1 B Feb.
7 user2 2021-6-26 Q2 … …

 註意，上述Q2用戶填瞭2個選項。 本身問卷設定就是支持多選的。 用JSON文檔結構保存數據， 主要是為瞭方便采集和數據存取。因此要額外做些數據處理， 使采集的數據便於統計。

筆者經過一些調查， 發現可以結合使用UNPIVOT和OPENJSON方法來達到理想的效果。 具體過程如下：

準備表格和初始化數據

-- 1 create table
Create Table T_Questionaire(id int identity(1,1) primary key, username varchar(100), t1 nvarchar(500),t2 nvarchar(500),t3 nvarchar(500), dt datetime)


-- 2 init data
Insert into T_Questionaire( username, t1, t2, t3, dt) 
values ('John' , '[{"code":"a", "desc":"Monday"}]', '[{"code":"a", "desc":"Jan."}]', '[{"code":"b", "desc":"2021"}]' ,getdate())
 ,     ('Alice' , '[{"code":"b", "desc":"Tuesday"}]', '[{"code":"a", "desc":"Jan."}, {"code":"b", "desc":"Feb."}]', '[{"code":"a", "desc":"2020"},{"code":"b", "desc":"2021"}]' ,getdate())

數據內容：

 創建轉換視圖：

Create   or alter view V_VerticalQuestionaire 
as
with pt as (
select a.username, a.T, a.answers,  a.dt from dbo.T_Questionaire a
unpivot 
  (  answers for T in (t1,t2,t3  ))
a)
select pt.username, pt.dt, pt.T , aw.code, aw.[desc]
from pt 
  cross apply openjson(answers) WITH (code NVARCHAR(100) '$.code', [desc] NVARCHAR(100) '$.desc') aw

查詢結果如下：

 總結下解決的思路：

1 先用unpivot將列行轉換， 使橫表記錄變成縱表記錄

2 使用openjson 將json數據轉換為集合數據， 然後使用cross apply 將集合展開

好瞭，到此這篇關於SQLServer中JSON文檔型數據的查詢問題解決的文章就介紹到這瞭,更多相關SQLServer中JSON數據查詢內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：