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编写archive通用模板脚本实现自动分批删除数据)
- sqlserver统计表大小(SqlServer 垂直分表减少程序改动)
- sqlserver新建表参数错误(Sql Server中判断表、列不存在则创建的方法)
- sqlserver索引策略(SQLServer性能优化--间接实现函数索引或者Hash索引)
- sqlserver备份还原地址(SqlServer高版本数据备份还原到低版本)
- SQLServer设置客户端使用IP地址登录的图文详解(SQLServer设置客户端使用IP地址登录的图文详解)
- sqlserver游标使用场景(解析SQL Server聚焦移除Bookmark Lookup、RID Lookup、Key Lookup)
- SQLServer将数据导出为SQL脚本
- centos系统mysql安装及连接教程(CentOS8安装SQLServer2019的过程)
- sqlserver查询表结构(sql server递归子节点、父节点sql查询表结构的实例)
- sqlserver触发器修改当前字段(利用SQL Server触发器实现表的历史修改痕迹记录)
- sqlserver纵表变横表(SQL Server行转列的方法解析)
- 完整版sqlserver2019卸载教程(Sql Server 2012完全卸载方法 只需8步轻松卸载)
- sqlserver如何查找值为null的数据(SQL Server中NULL的正确使用与空间占用)
- sqlserver字符串截取填充(SQL Server实现split函数分割字符串功能及用法示例)
- SqlServer类似正则表达式的字符处理问题(SqlServer类似正则表达式的字符处理问题)
- 金球奖只青睐那些会戴珠宝的女人(金球奖只青睐那些会戴珠宝的女人)
- 浙江省一个县,人口超40万,建县历史超1100年(浙江省一个县人口超40万)
- 五代十国南唐历代国君(五代十国南唐历代国君)
- 飞机引进工程师杨隆 匠人匠心,只争朝夕(飞机引进工程师杨隆)
- 三人行,她们是育人路上的 铁三角 团队(她们是育人路上的)
- 阴阳师 孟婆山兔CP不倒 新皮肤草稿 孟婆兔 让痒痒鼠点赞(阴阳师孟婆山兔CP不倒)
热门推荐
- vue element 权限管理(Vue Element前端应用开发之功能点管理及权限控制)
- 前端ui 滑动条(AmazeUI 手机版页面的顶部导航条Header与侧边导航栏offCanvas的示例代码)
- 更改docker容器的ip地址(docker容器通过ping直接运行获取公网IP操作)
- mysql的浮点数类型(浅谈MySQL中float、double、decimal三个浮点类型的区别与总结)
- vue界面自动生成(Vue中实现3D标签云的详细代码)
- redis在php怎么使用(Windows服务器中PHP如何安装redis扩展)
- mysql自增长语句(mysql实现自增序列的示例代码)
- docker中mysql连接命令(docker 如何修改mysql的root密码)
- websocket聊天室怎么设置(基于websocket实现简单聊天室对话)
- php用什么解压(php解压缩zip和rar压缩包文件的方法)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9