当前位置:数据库 > > 正文

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

时间: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 来实现对结果的统计。有如下几个问题:

    比较适合分析的数据结构应该长这样:

  • 行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文档型数据的查询问题解决(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文档型数据的查询问题解决(SQLServer中JSON文档型数据的查询问题解决)

     总结下解决的思路:

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

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

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

    上一篇下一篇

    猜您喜欢

    热门推荐