SQLServer中JSON文档型数据的查询问题解决_Sql Server

SQLServer中JSON文档型数据的查询问题解决

2021-08-15 22:26Weizheng Sql Server

SQL Server 对于数据平台的开发者来说越来越友好,下面这篇文章主要给大家介绍了关于SQLServer中JSON文档型数据的查询问题的解决方法,需要的朋友可以参考下

近日在项目中遇到一个问题：如何在报表中统计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())

数据内容：

SQLServer中JSON文档型数据的查询问题解决

创建转换视图：

				?

									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