• 售前

  • 售后

热门帖子
入门百科

SQLServer中JSON文档型数据的查扣问题办理

[复制链接]
Gordon520 显示全部楼层 发表于 2021-8-14 14:31:58 |阅读模式 打印 上一主题 下一主题
克日在项目中遇到一个题目: 如安在报表中统计JSON格式存储的数据?
比方有个观察问卷记录表,记录每个题目的答案。 其布局表现如下(横表计划)
IduserdateQ1_AnswerQ2_AnswerQ3_Answer
行Id答题用户答题日期题目一结果题目二结果题目三结果
在[Q1_Answer]、[Q2_Answer]、[Q3_Answer]中记录的数据格式是JSON文档内容,由于是选项值,而且思量到大概有多选, 所以存储的格式如下:
  1. 1 [
  2.      {"code":"a", "desc":"Jan."},
  3.      {"code":"b", "desc":"Feb."}
  4.   ]
复制代码
此中 code 表现选项, desc 表现选项的文字形貌。
如今,用户想用PowerBI 来实现对结果的统计。有如下几个题目:
       
  • 在Power BI中,无法直接从JSON数据中读取到选项值   
  • 假如是多选,又该如那边置惩罚。
比力适合分析的数据布局应该长这样:
行Id答题用户答题日期题目编号用户选项选项文字
1user12021-6-26Q1AJan.
2user12021-6-26Q2AMon.
3user12021-6-26Q2BTue.
4user12021-6-26Q3ASwimming
6user22021-6-26Q1BFeb.
7user22021-6-26Q2......
注意,上述Q2用户填了2个选项。 本身问卷设定就是支持多选的。 用JSON文档布局保存数据, 主要是为了方便收罗和数据存取。因此要额外做些数据处理惩罚, 使收罗的数据便于统计。
笔者颠末一些观察, 发现可以联合利用UNPIVOT和OPENJSON方法来到达理想的结果。 具体过程如下:
预备表格和初始化数据
  1. -- 1 create table
  2. 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)
  3. -- 2 init data
  4. Insert into T_Questionaire( username, t1, t2, t3, dt)
  5. values ('John' , '[{"code":"a", "desc":"Monday"}]', '[{"code":"a", "desc":"Jan."}]', '[{"code":"b", "desc":"2021"}]' ,getdate())
  6. ,     ('Alice' , '[{"code":"b", "desc":"Tuesday"}]', '[{"code":"a", "desc":"Jan."}, {"code":"b", "desc":"Feb."}]', '[{"code":"a", "desc":"2020"},{"code":"b", "desc":"2021"}]' ,getdate())
复制代码
数据内容:

创建转换视图:
  1. Create   or alter view V_VerticalQuestionaire
  2. as
  3. with pt as (
  4. select a.username, a.T, a.answers,  a.dt from dbo.T_Questionaire a
  5. unpivot
  6.   (  answers for T in (t1,t2,t3  ))
  7. a)
  8. select pt.username, pt.dt, pt.T , aw.code, aw.[desc]
  9. from pt
  10.   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数据查询内容请搜刮脚本之家从前的文章或继续欣赏下面的相干文章渴望各人以后多多支持脚本之家!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作