Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

SonOfJeffGoldblum
New Contributor II

sql json data

I am working with a MySQL Server db that contains JSON data. I am trying to get data from the JSON portion to pivot into a column AND to extract data from there as well. 

My JSON field is [AnswersJson]: [{"AnswerPart":null,"AnswerColumn":null,"AnswerText":"a","AnswerOther":null,"AnswerExportValue":null,"AnswerComment":null}]  where I need to pull '$.AnswerText', sometimes 'AnswerOther', and sometimes '$.AnswerExportValue'

The field I want to turn into a column heading is [Survey].

There is another table that has a JSON field, [JsonData] which has some elements for a person (FirstName, LastName, Generation, Age, etc). This is where the subquery comes into play as I was able to make work within SQL Server. Each time I add to data load editor, a good portion does not get recognized due to the (') and cannot figure out thogh a straight copy and paste does not result in anything pulled as it does in SQL Server.

I have 18 different survey questions that come through, so doing a constant if(SurveyNumber=1, [SurveyQuestion]) looks ugly as well inefficient if a survey text is updated.

I would love it if the result could show:

IDSurveyQuestion1SurveyQuestion2SurveyQuestion3SurveyQuestion4SurveyQuestion5SurveyQuestion6SurveyQuestion7SurveyQuestion8SurveyQuestion9
1AnswerText1AnswerText2AnswerText3AnswerText4AnswerText5AnswerText6AnswerText7AnswerText8AnswerText9
          

where SurveyQuestion1 is the first question with the associated AnswerText, SurveyQuestion2 is the second question and AnswerText all the way through to question 18.

Is there anything that can do this?

Example code is below that I have tested with in various ways.

 

SQL
SET NOCOUNT ON
DECLARE
@cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SELECT
let @cols = STUFF((SELECT distinct ',' + QUOTENAME(Survey) FROM [SurveyQuestion].[dbo].[QuestionDetails]).value('.', 'NVARCHAR(MAX)'),1,1,'')
let @query = (SELECT [Responder_Id],CREATED,FirstName,LastName,Age2,Generation, ' + @cols + '
FROM
(SELECT
[Responder_Id]
,DETAILS.[CreateDateTime] AS CREATED
,JSON_VALUE(JsonData, ''$.FirstName'') AS FirstName
,JSON_VALUE(JsonData, ''$.LastName'') AS LastName
,JSON_VALUE(JsonData, ''$.Age'') AS Age2
--,null AS Generation
,[Survey]
,[AnswersJson]
FROM
[SurveyQuestion].[dbo].[QuestionDetails] AS DETAILS
INNER JOIN Responder AS RESPONDER ON RESPONDER.Id = DETAILS.Responder_Id

) x
pivot
(max([AnswersJson])
for[Survey] in (' + @cols + ')) p

execute sp_executesql @query;

Help me obi wan Kenobi, you are my only hope

Everything not saved will be lost
— Nintendo Quit screen message