SQLServer中JSON文档型数据的查询问题解决

 更新时间:2021年7月16日 10:00  
SQL Server 对于数据平台的开发者来说越来越友好,下面这篇文章主要给大家介绍了关于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 错误: 15404,无法获取有关 Windows NT 组/用户 WIN-8IVSNAQS8T7\Administrator 的信息

    SQLServer 错误: 15404,无法获取有关 Windows NT 组/用户 'WIN-8IVSNAQS8T7\Administrator' 的信息,错误代码 0x534。 [SQLSTATE 42000] (ConnIsLoginSysAdmin)...2021-07-15
  • Node实现搜索框进行模糊查询

    这篇文章主要为大家详细介绍了Node实现搜索框进行模糊查询,文中示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2021-06-28
  • 教你怎么用python连接sql server

    今天教大家怎么用python连接sql server,文中有非常详细的代码示例,对正在学习python的小伙伴们很有帮助,需要的朋友可以参考下...2021-05-18
  • JSON,AJAX,Maven入门基础

    这篇文章主要介绍了JSON,AJAX和Maven基础,如何使用AJAX读取Json数组里面的数据,感兴趣的小伙伴们可以参考一下,希望能够帮助到你...2021-07-13
  • mysql 带多个条件的查询方式

    这篇文章主要介绍了mysql 带多个条件的查询方式,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐教...2021-06-04
  • SpringBoot+MyBatisPlus+MySQL8实现树形结构查询

    这篇文章主要为大家详细介绍了SpringBoot+MyBatisPlus+MySQL8实现树形结构查询,文中示例代码介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2021-06-07
  • FastJSON字段智能匹配踩坑的解决

    这篇文章主要介绍了FastJSON字段智能匹配踩坑的解决方案,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐教...2021-06-18
  • 解析C#拼接Json串的几种方法

    本文主要介绍了C#拼接Json串的几种方法,主要包括了4种方式,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2021-06-01
  • 替换json对象中的key最佳方案

    本文给大家介绍如何替换json对象中的key,通过实例代码给大家介绍key的替换方法,代码也很简单,需要的朋友参考下吧...2021-06-02
  • Java中常用解析工具jackson及fastjson的使用

    今天给大家带来的是关于Java解析工具的相关知识,文章围绕着jackson及fastjson的使用展开,文中有非常详细的介绍及代码示例,需要的朋友可以参考下...2021-06-28
  • 详解MySQL 联合查询优化机制

    MySQL 使用联合的形式的地方会远远超过我们过去认知的范畴。基本上,它会认为每个查询都有联合,而不仅仅是从两张表中查出匹配的数据行,这包括了子查询,甚至仅仅对单表的 SELECT 操作。因此,理解 MySQL 如何执行联合十分重要。...2021-05-10
  • 深入探究Mysql模糊查询是否区分大小写

    这篇文章主要给大家介绍了关于Mysql模糊查询是否区分大小写的相关资料,文中给出了5种解决方法以及各个方法的建议,需要的朋友可以参考下...2021-06-11
  • jackson json序列化实现首字母大写,第二个字母需小写

    这篇文章主要介绍了jackson json序列化实现首字母大写,第二个字母需小写方式,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐教...2021-06-29
  • ssm框架+PageHelper插件实现分页查询功能

    今天小编教大家如何通过ssm框架+PageHelper插件实现分页查询功能,首先大家需要新建一个maven工程引入jar包,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧...2021-06-10
  • django中F与Q查询的使用

    一般查询都是单条件查询,F和Q是组合条件查询,本文主要介绍了django中F与Q查询的使用,感兴趣的可以了解一下...2021-06-23
  • IDEA 链接Mysql数据库并执行查询操作的完整代码

    这篇文章主要介绍了IDEA 链接Mysql数据库并执行查询操作的完整代码,代码不难,详细大家看完本文肯定有意向不到的收获,感兴趣的朋友跟随小编一起看看吧...2021-05-20
  • SQLServer中JSON文档型数据的查询问题解决

    SQL Server 对于数据平台的开发者来说越来越友好,下面这篇文章主要给大家介绍了关于SQLServer中JSON文档型数据的查询问题的解决方法,需要的朋友可以参考下...2021-07-16
  • 解决springboot整合cxf-jaxrs中json转换的问题

    这篇文章主要介绍了解决springboot整合cxf-jaxrs中json转换的问题,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐教...2021-07-09
  • Java基础之FastJson详解

    今天给大家复习Java基础FastJson,文中有非常详细的代码示例,对正在学习java的小伙伴们有很好地帮助,需要的朋友可以参考下...2021-05-18
  • Mybatis-Plus查询中如何排除标识字段

    这篇文章主要介绍了Mybatis-Plus查询中排除标识字段的操作,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐教...2021-06-21