一个SQLServer中JSON文档型数据的查询问题

  • Z时代
  • 2024-01-10
  • 分类：综合

database

近日在项目中遇到一个问题： 如何在报表中统计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
CreateTable T_Questionaire(id intidentity(1,1) primarykey, username varchar(100), t1 nvarchar(500),t2 nvarchar(500),t3 nvarchar(500), dt datetime)
-- 2 init data
Insertinto 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())

 

数据内容：

 

 

 

 

 

 

 

 

 创建转换视图：

Createoralterview 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文档型数据的查询问题 的全部内容， 来源链接： utcz.com/z/535731.html

回到顶部