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数据查询内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • SQL Server中将查询结果转换为Json格式脚本分享

    脚本源码: 复制代码 代码如下: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE[dbo].[SerializeJSON]( @ParameterSQL AS VARCHAR(MAX) ) AS BEGIN    DECLARE @SQL NVARCHAR(MAX) DECLARE @XMLString VARCHAR(MAX) DECLARE @XML XML DECLARE @Paramlist NVARCH

  • SqlServer将查询结果转换为XML和JSON

    很久之前用到的,现在整理在这,里面一些代码来源于网上,不过有些bug已被我修改了. 1.查询结果转XML DECLARE @ParameterSQL NVARCHAR(MAX)='SELECT * FROM table'; DECLARE @SQL NVARCHAR(MAX) DECLARE @XMLString VARCHAR(MAX) DECLARE @XML XML DECLARE @Paramlist NVARCHAR(1000) SET @Paramlist = N'@XML XML

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

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

    近日在项目中遇到一个问题: 如何在报表中统计JSON格式存储的数据? 例如有个调查问卷记录表,记录每个问题的答案. 其结构示意如下(横表设计) Id user date Q1_Answer Q2_Answer Q3_Answer 行Id 答题用户 答题日期 问题一结果 问题二结果 问题三结果 在[Q1_Answer].[Q2_Answer].[Q3_Answer]中记录的数据格式是JSON文档内容,因为是选项值,而且考虑到可能有多选, 所以存储的格式如下: 1 [ {"code":&q

  • python中如何将一个JSON文档映射为Python对象

    python中如何将一个JSON文档映射为Python对象

    目录 一.将一个JSON文档映射为Python对象 第一种方法 第二种方法 二.总结 前言: 文章主要包括一下三个部分: 考点:loads函数的用法. 面试题:如何将一个JSON文档映射为Python对象? 解析 一.将一个JSON文档映射为Python对象 第一种方法 供我们读取的JSON文件是test.json,文件内容如下: { "name": "孤寒者", "age": 18, "height": 130 } 首先,我

  • PHP库 查询Mongodb中的文档ID的方法

    在IBM我的一份新工作是一名开发的后勤人员.那意味着我的大部分时间是在和数据库打交道.在我的工作流程中,我花了一些时间在MongoDB上面--这是一个文档数据库.但是在通过ID来检索记录这个操作上面我碰到了一些问题.下面的代码是最终版本,以后碰到类似的问题我可以直接引用它.如果大家也需要,希望下面对大家有所帮助. MongoDB 和 IDs 当我向一个集合中插入数据的时候,我并没有设置_id字段:如果这个字段是空的话,那么MongoDB将要自动生成一个ID来使用,这对我来说是非常不错的.然而,当

  • MongoDB中对文档的增删查改基本操作方法总结

    插入文档:insert() 方法 要插入数据到 MongoDB 集合,需要使用 MongoDB 的  insert() 或 save() 方法. 语法: insert() 命令的基本语法如下: >db.COLLECTION_NAME.insert(document) 例子:  >db.mycol.insert({    _id: ObjectId(7df78ad8902c),    title: 'MongoDB Overview',     description: 'MongoDB is

  • 关于JS与jQuery中的文档加载问题

    jquery中的$(document).ready()类似于javascript中的window.onload(),但是其中还是有很大区别的 1.jquery中的可以简化为$().ready(),$(function),   而js却不行必须全部写完. 2.同时js的window.onload()只能有一个,而jquery的$(document).ready()却可以有多个. 3.最重要的一点是window.onload必须等到页面内包括图片的所有元素加载完毕后才能执行.   $(documen

  • 对tensorflow中cifar-10文档的Read操作详解

    前言 在tensorflow的官方文档中得卷积神经网络一章,有一个使用cifar-10图片数据集的实验,搭建卷积神经网络倒不难,但是那个cifar10_input文件着实让我费了一番心思.配合着官方文档也算看的七七八八,但是中间还是有一些不太明白,不明白的mark一下,这次记下一些已经明白的. 研究 cifar10_input.py文件的read操作,主要的就是下面的代码: if not eval_data: filenames = [os.path.join(data_dir, 'data_b

  • Java程序中Doc文档注释示例教程

    Java程序中Doc文档注释示例教程

    目录 Doc注释规范 @符号的用处 如何生成Doc文档 第一个:Dos命令生成 第二个:IDE工具生成 许多人写代码时总不喜欢写注释,每个程序员如此,嘿嘿,我也一样 不过,话说回来,该写还是要写哦!没人会喜欢一个不写注释的程序员,当然,也没有一个喜欢写注释的程序员,今天,我们就来说说Java注释之一--Doc注释 我们知道,Java支持 3 种注释,分别是单行注释.多行注释和文档注释,我们来看看他们的样子 //单行注释   /* 多行注释 */   /** *@... *.... *文档注释 *

  • MongoDB集合中的文档管理

    MongoDB集合中的文档管理

    上一博客写了集合的管理,集合里面存放的是文档,因此聪明的你应该能想到这篇是学习文档管理.要说标题应该是文档管理,不过对于文档的管理都是先获得集合对象,在集合对象上调用方法管理文档,所以标题还是对集合的管理. 在集合管理中,要管理集合首先要获得这个集合,同样文档管理也是要先获得集合对象,然后通过集合对象管理文档. 一.将文档添加到集合中 为什么要先说添加呢,因为不添加就没法说下面的查找.将文档添加到一个集合中,首先需要得到集合对象,通过几个对象调用insert(document)或save(doc

  • .NET中开源文档操作组件DocX的介绍与使用

    前言 相信大家应该都有所体会,在目前的软件项目中,都会较多的使用到对文档的操作,用于记录和统计相关业务信息.由于系统自身提供了对文档的相关操作,所以在一定程度上极大的简化了软件使用者的工作量. 在.NET项目中如果用户提出了相关文档操作的需求,开发者较多的会使用到微软自行提供的插件,在一定程度上简化了开发人员的工作量,但是同时也给用户带来了一些困扰,例如需要安装庞大的office,在用户体验性就会降低很多,并且在国内,很多人都还是使用wps,这就导致一部分只安装了wps的使用者很是为难,在对Ex

  • 介绍Python中的文档测试模块

    如果你经常阅读Python的官方文档,可以看到很多文档都有示例代码.比如re模块就带了很多示例代码: >>> import re >>> m = re.search('(?<=abc)def', 'abcdef') >>> m.group(0) 'def' 可以把这些示例代码在Python的交互式环境下输入并执行,结果与文档中的示例代码显示的一致. 这些代码与其他说明可以写在注释中,然后,由一些工具来自动生成文档.既然这些代码本身就可以粘贴出来直

随机推荐