SQLServer中JSON文档型数据的查询问题解决(SQLServer中JSON文档型数据的查询问题解决)
类别:数据库 浏览量:2964
时间:2021-09-29 01:22:03 SQLServer中JSON文档型数据的查询问题解决
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 将集合展开
好了,到此这篇关于SQLServer中JSON文档型数据的查询问题解决的文章就介绍到这了,更多相关SQLServer中JSON数据查询内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
您可能感兴趣
- sqlserver日期型数据类型是哪个(SQL Server时间戳功能与用法详解)
- sqlserver降序排列(SQL SERVER临时表排序问题的解决方法)
- sqlserver游标实例(Sql Server临时表和游标的使用小结)
- sqlserver语句中的temp(SQL SERVER中强制类型转换cast和convert的区别详解)
- sqlserver2016安装教程(SQL server 2016 安装步骤图文教程)
- sqlserver使用简介(SQL Server Page结构深入分析)
- sqlserver索引介绍(浅析SQL Server的聚焦使用索引和查询执行计划)
- sqlserver字符串格式化(SQL server中字符串逗号分隔函数分享)
- sqlserver管理工具远程连接(MSSQLSERVER不同版本设置开启远程连接sa配置)
- 详解SqlServer数据库中Substring函数的用法(详解SqlServer数据库中Substring函数的用法)
- sqlserver2016的安装(Sql Server2016 正式版安装程序图解教程)
- sql中去除重复记录的关键字(sqlserver查询去掉重复数据的实现)
- sqlserver怎么手动添加数据库表(SQL Server 数据库调整表中列的顺序操作方法及遇到问题)
- sqlserver2008远程连接设置(如何开启SqlServer 远程访问)
- SQLServer设置客户端使用IP地址登录的图文详解(SQLServer设置客户端使用IP地址登录的图文详解)
- 怎么开放sql server端口(SQLServer2019配置端口号的实现)
- 王铲铲的致富之路无限金币卡法攻略教学(王铲铲的致富之路无限金币卡法攻略教学)
- 文明6金币太少怎么办 文明6无限刷钱教程(文明6金币太少怎么办)
- 开国中将,王牌军63军首任政委,两个连襟一个上将一个少将传为佳话(王牌军63军首任政委)
- 臭名昭著的731部队最高负责人 石井四郎(臭名昭著的731部队最高负责人)
- 王牌部队,你看的剧情我看的时尚(你看的剧情我看的时尚)
- 被鉴定的古董价值300万 当心,你可能遇到诈骗了(被鉴定的古董价值300万)
热门推荐
- mysql 死锁产生的原因和必要条件(Mysql查看死锁与解除死锁的深入讲解)
- css 页面加载样式(如何只在IE上加载CSS样式表)
- 常见的mysql优化策略(MySQL pt-slave-restart工具的使用简介)
- css如何设置表格样式
- windows server 2008r2怎么安装(Windows Server2008 R2 MVC 环境安装配置教程)
- sql server2005如何配置(SQLServer2005创建定时作业任务)
- linux下安装nginx常见问题(Linux安装Nginx步骤详解)
- nginx更改html内容(apache与iis下让html格式的页面也同样具有shtml的动态解析)
- jquery中append和appendto的区别
- Jquery实现table表格行的添加、删除
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9