近日在项目中遇到一个问题: 如何在报表中统计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 将集合展开
好了,到此这篇关于sqlserver中json文档型数据的查询问题解决的文章就介绍到这了,更多相关sqlserver中json数据查询内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com!