A query problem of JSON document data in SQL Server
Weizheng 2021-06-28 03:52:37

Recently, I encountered a problem in the project : How to make statistics in reports JSON Data stored in different formats ?


For example, there is a questionnaire record , Record the answers to each question . Its structure is shown as follows ( Horizontal table design )

Id user date Q1_Answer Q2_Answer Q3_Answer
That's ok Id Answer users Answer date Question 1 Results Question 2 Results Question 3 Results

stay [Q1_Answer]、[Q2_Answer]、[Q3_Answer] The data format recorded in is JSON Document content , Because it's an option value , And considering that there may be multiple choices , So the storage format is as follows :

1 [ 
{"code":"a", "desc":"Jan."},
{"code":"b", "desc":"Feb."}

among code Said options , desc A text description of an option .

Now? , Users want to use PowerBI To realize the statistics of the results . There are several questions :

  • stay Power BI in , Not directly from JSON The option value is read from the data
  • If it's multiple choice , How to deal with .

The data structure suitable for analysis should look like this :


That's ok Id Answer users Answer date Question number User options Option text
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 ... ...


  Be careful , Above Q2 The user filled in 2 An option . The questionnaire itself supports multiple choices . use JSON The document structure holds the data , It is mainly for the convenience of collection and data access . So we need to do some extra data processing , Make the collected data convenient for statistics .


After some investigation , It can be used in combination UNPIVOT and OPENJSON Methods to achieve the desired effect . The specific process is as follows :

Preparing tables and initializing data

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


The data content :









  Create transformation view :

Create or alter view V_VerticalQuestionaire
with pt as (
select a.username, a.T, a.answers, a.dt from dbo.T_Questionaire a
( answers for T in (t1,t2,t3 ))
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

The query results are as follows :





  Summarize the solution :

1 First use unpivot Convert columns to rows , Change horizontal table record into vertical table record

2 Use openjson take json Data to aggregate data , And then use cross apply Expand the collection


Please bring the original link to reprint ,thank
Similar articles