sql json data

SonOfJeffGoldblum — Sat, 16 Nov 2024 20:16:23 GMT

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:

ID	SurveyQuestion1	SurveyQuestion2	SurveyQuestion3	SurveyQuestion4	SurveyQuestion5	SurveyQuestion6	SurveyQuestion7	SurveyQuestion8	SurveyQuestion9
1	AnswerText1	AnswerText2	AnswerText3	AnswerText4	AnswerText5	AnswerText6	AnswerText7	AnswerText8	AnswerText9

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

topic sql json data in QlikView

sql json data