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

 

数据内容:

 

 

 

 

 

 

 

 

 创建转换视图:

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 将集合展开

 

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐