SQLServer中JSON文档型数据的查询问题解决
作者:Weizheng 时间:2024-01-19 00:41:49
近日在项目中遇到一个问题: 如何在报表中统计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 将集合展开
来源:https://www.cnblogs.com/huwz/p/14934180.html
标签:sqlserver,json,查询
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
python3用urllib抓取贴吧邮箱和QQ实例
2022-05-15 16:25:18
php生成图片验证码的实例讲解
2023-09-11 21:36:29
![](https://img.aspxhome.com/file/2023/4/55574_0s.png)
图文详解Python中模块或py文件导入(超详细!)
2023-01-13 01:41:13
![](https://img.aspxhome.com/file/2023/5/90565_0s.png)
如何将 jQuery 从你的 Bootstrap 项目中移除(取而代之使用Vue.js)
2023-07-02 17:08:10
Python返回数组/List长度的实例
2023-10-12 14:03:28
利用Python爬取微博数据生成词云图片实例代码
2023-03-20 15:49:59
![](https://img.aspxhome.com/file/2023/2/133472_0s.jpg)
如何利用Python写猜数字和字母的游戏
2021-01-28 00:00:09
![](https://img.aspxhome.com/file/2023/8/101258_0s.png)
python中使用smtplib和email模块发送邮件实例
2022-09-16 05:37:23
python应用文件读取与登录注册功能
2023-04-17 17:04:03
Python函数中4种参数的使用教程
2023-03-06 23:06:03
详解MySQL中InnoDB的存储文件
2024-01-13 04:58:00
![](https://img.aspxhome.com/file/2023/5/118195_0s.jpg)
Python快速实现一个线程池的示例代码
2021-06-01 04:51:01
![](https://img.aspxhome.com/file/2023/6/128246_0s.png)
对vue.js中this.$emit的深入理解
2024-04-26 17:40:12
python线程、进程和协程详解
2023-03-02 14:00:39
4种JavaScript实现简单tab选项卡切换的方法
2024-02-23 10:25:51
![](https://img.aspxhome.com/file/2023/0/56530_0s.jpg)
Pytorch中实现CPU和GPU之间的切换的两种方法
2021-08-21 07:24:18
vue实现小球滑动交叉效果
2024-04-30 10:30:09
Python实现无损放大图片的示例代码
2022-12-15 12:50:12
![](https://img.aspxhome.com/file/2023/7/114677_0s.jpg)
Linux mysql-5.6如何实现重置root密码
2024-01-27 16:48:29
![](https://img.aspxhome.com/file/2023/5/82875_0s.png)
Python不支持 i ++ 语法的原因解析
2022-02-07 12:16:49
![](https://img.aspxhome.com/file/2023/7/67237_0s.jpg)