快捷導(dǎo)航

SQLServer中JSON文檔型數(shù)據(jù)的查詢問題解決

更新時間：2021年06月27日 12:05:16 作者：Weizheng

SQL Server 對于數(shù)據(jù)平臺的開發(fā)者來說越來越友好,下面這篇文章主要給大家介紹了關(guān)于SQLServer中JSON文檔型數(shù)據(jù)的查詢問題的解決方法,需要的朋友可以參考下

近日在項目中遇到一個問題：如何在報表中統(tǒng)計JSON格式存儲的數(shù)據(jù)？

例如有個調(diào)查問卷記錄表，記錄每個問題的答案。其結(jié)構(gòu)示意如下（橫表設(shè)計）

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

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

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

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

現(xiàn)在，用戶想用PowerBI 來實現(xiàn)對結(jié)果的統(tǒng)計。有如下幾個問題：

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

比較適合分析的數(shù)據(jù)結(jié)構(gòu)應(yīng)該長這樣：

行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個選項。本身問卷設(shè)定就是支持多選的。用JSON文檔結(jié)構(gòu)保存數(shù)據(jù)，主要是為了方便采集和數(shù)據(jù)存取。因此要額外做些數(shù)據(jù)處理，使采集的數(shù)據(jù)便于統(tǒng)計。

筆者經(jīng)過一些調(diào)查，發(fā)現(xiàn)可以結(jié)合使用UNPIVOT和OPENJSON方法來達到理想的效果。具體過程如下：

準(zhǔn)備表格和初始化數(shù)據(jù)

-- 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())

數(shù)據(jù)內(nèi)容：

創(chuàng)建轉(zhuǎn)換視圖：

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

查詢結(jié)果如下：

總結(jié)下解決的思路：

1 先用unpivot將列行轉(zhuǎn)換，使橫表記錄變成縱表記錄

2 使用openjson 將json數(shù)據(jù)轉(zhuǎn)換為集合數(shù)據(jù)，然后使用cross apply 將集合展開

好了，到此這篇關(guān)于SQLServer中JSON文檔型數(shù)據(jù)的查詢問題解決的文章就介紹到這了,更多相關(guān)SQLServer中JSON數(shù)據(jù)查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: