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())

数据内容:

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

创建转换视图:

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

查询结果如下:

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

总结下解决的思路:

1 先用unpivot将列行转换, 使横表记录变成纵表记录

2 使用openjson 将json数据转换为集合数据, 然后使用cross apply 将集合展开

好了,到此这篇关于SQLServer中JSON文档型数据的查询问题解决的文章就介绍到这了,更多相关SQLServer中JSON数据查询内容请搜索179885.Com以前的文章或继续浏览下面的相关文章希望大家以后多多支持179885.Com!

猜你在找的SQLServer中JSON文档型数据的查询问题解决相关文章

SQL Server 对于数据平台的开发者来说越来越友好,下面这篇文章主要给大家介绍了关于SQLServer中JSON文档型数据的查询问题的解决方法,需求的大佬可以参考下
了解决sql server 数据库,sa用户被锁定的问题,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐教
在日常开发过程中你深入了解过sql数据库中的null值问题吗?今天小编抽空给大家分享在 SQL 语句中处理 NULL 值的方法,感兴趣的朋友跟随小编一起看看吧
Mod(a,b) 在sql中的意思是a/b的余数,本文详细的介绍了sql中mod()函数取余数的用法,感兴趣的小伙伴们可以参考一下
SQL查询时会经常需要,把查询的结果拼接成一个字符串。那么怎么直接把sql查询结果列拼接成逗号分隔的字符串方法,下面就一起来了解一下
SQL注入攻击是黑客对数据库进行攻击常用的手段之一,本文主要介绍了如何如何有效防止sql注入,需要的朋友们下面随着小编来一起学习学习吧
sql语句在程序开发时使用非常广泛,本文给大家收藏自增,增删改查,多表查询,内置函数等实用 SQL 语句,给大家整理的很详细,喜欢的朋友快快收藏起来吧
SQL 各部分的逻辑执行顺序 注意到窗口函数的求值仅仅位于ORDER BY之前,而位于 SQL 的绝大部分之后。本文重点给大家介绍SQL 窗口函数实现高效分页查询功能,通过案
在日常使用数据库时,NULL值在数据库中是一个很特殊且有趣的存在,本文介绍了在SQL中该如何处理NULL值,感兴趣的可以了解一下
了SQL Server 2014 数据库中文版安装图文教程,需求的大佬可以参考下
了mybatis调用sqlserver存储过程返回结果集的方法,本文分两种方法给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需求的大佬可以参考下
了SQL Server2019数据库之简单子查询的具有方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习