主頁(yè) > 知識(shí)庫(kù) > SQLServer中JSON文檔型數(shù)據(jù)的查詢問題解決

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

熱門標(biāo)簽:萬利達(dá)綜合醫(yī)院地圖標(biāo)注點(diǎn) 武漢AI電銷機(jī)器人 電銷機(jī)器人 深圳 在電子版地圖標(biāo)注要收費(fèi)嗎 實(shí)體店地圖標(biāo)注怎么標(biāo) 地圖標(biāo)注如何弄全套標(biāo) 外呼系統(tǒng)會(huì)封嗎 股票配資電銷機(jī)器人 南京電銷外呼系統(tǒng)哪家好

近日在項(xiàng)目中遇到一個(gè)問題: 如何在報(bào)表中統(tǒng)計(jì)JSON格式存儲(chǔ)的數(shù)據(jù)?

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

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

在[Q1_Answer]、[Q2_Answer]、[Q3_Answer]中記錄的數(shù)據(jù)格式是JSON文檔內(nèi)容,因?yàn)槭沁x項(xiàng)值,而且考慮到可能有多選, 所以存儲(chǔ)的格式如下:

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

其中 code 表示選項(xiàng), desc 表示選項(xiàng)的文字描述。

現(xiàn)在,用戶想用PowerBI 來實(shí)現(xiàn)對(duì)結(jié)果的統(tǒng)計(jì)。有如下幾個(gè)問題:

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

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

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

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

準(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)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!

您可能感興趣的文章:
  • SQL Server中將查詢結(jié)果轉(zhuǎn)換為Json格式腳本分享
  • SqlServer將查詢結(jié)果轉(zhuǎn)換為XML和JSON

標(biāo)簽:泰安 廣東 濟(jì)寧 臺(tái)州 濟(jì)源 武威 安徽 汕頭

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《SQLServer中JSON文檔型數(shù)據(jù)的查詢問題解決》,本文關(guān)鍵詞  SQLServer,中,JSON,文檔型,數(shù)據(jù),;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《SQLServer中JSON文檔型數(shù)據(jù)的查詢問題解決》相關(guān)的同類信息!
  • 本頁(yè)收集關(guān)于SQLServer中JSON文檔型數(shù)據(jù)的查詢問題解決的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章

    上一篇:sql字段解析器的實(shí)現(xiàn)示例

    下一篇:SQL 尚未定義空閑 CPU 條件 - OnIdle 作業(yè)計(jì)劃將不起任何作用