Arnaldo Antonio Torres Arnaldo Antonio Torres - 1 month ago 6
JSON Question

Making json from sql query

I have the following scenario:

class question {
idQuestion: string;
question: string;
type: string;
}

class options {
idOption: string;
option: string;
}


My SQL returns:

idquestion question type idoption option


i.e:

question1 foo? textbox null null
question2 bar? select option1 aaa
question2 bar? select option2 bbb
question3 foobar? radio option1 aaa
question3 foobar? radio option2 bbb
question3 foobar? radio option3 ccc


I want to map the SQL response to the following interface:

questionOptions{
question: Question;
options: Option[];
}


How can I make it possible? So in the end, I could have a list of question, each one containing its options.

P.S: Would it be a better option to make the association between question and options from the sql database?

EDIT:

From the sample data I want to obtain the following json:

[
{
idQuestion: "question1",
question: "foo?",
options: []
},
{
idQuestion: "question2",
question: "bar?"
options: [
{
idOption: "option1",
option: "aaa"
},
{
idOption: "option2",
option: "bbb"
},
]
}
]

Answer

I have a helper function which will transform virtually any row/data set into a JSON String/Array.

Assuming 2012+

Declare @YourData table (idQuestion varchar(50),question varchar(50), type varchar(50),idOption varchar(50),[option] varchar(50))
Insert Into @YourData values
('question1','foo?','textbox', null, null),
('question2','bar?','select','option1','aaa'),
('question2','bar?','select','option2','bbb'),
('question3','foobar?','radio','option1','aaa'),
('question3','foobar?','radio','option2','bbb'),
('question3','foobar?','radio','option3','ccc')


Declare @JSON varchar(max) = ''
Select @JSON=@JSON+','+String
 From  (
        Select String=Replace(B.JSON,'}',',"options":'+IsNull(C.JSON,'[]')+'}')
         From (Select Distinct idquestion,question From @YourData) A
         Cross Apply (Select JSON=[dbo].[udf-Str-JSON](0,0,(Select A.idQuestion,A.question  for XML RAW))) B
         Cross Apply (Select JSON=[dbo].[udf-Str-JSON](0,0,(Select idOption,[option] from @YourData Where idquestion=A.idquestion  for XML RAW))) C
       ) A

Select '['+Stuff(@JSON,1,1,'')+']'

Returns

enter image description here

The UDF

CREATE FUNCTION [dbo].[udf-Str-JSON] (@IncludeHead int,@ToLowerCase int,@XML xml)
Returns varchar(max)
AS
Begin
    Declare @Head varchar(max) = '',@JSON varchar(max) = ''
    ; with cteEAV as (Select RowNr     =Row_Number() over (Order By (Select NULL))
                            ,Entity    = xRow.value('@*[1]','varchar(100)')
                            ,Attribute = xAtt.value('local-name(.)','varchar(100)')
                            ,Value     = xAtt.value('.','varchar(max)') 
                       From  @XML.nodes('/row') As R(xRow) 
                       Cross Apply R.xRow.nodes('./@*') As A(xAtt) )
          ,cteSum as (Select Records=count(Distinct Entity)
                            ,Head = IIF(@IncludeHead=0,IIF(count(Distinct Entity)<=1,'[getResults]','[[getResults]]'),Concat('{"status":{"successful":"true","timestamp":"',Format(GetUTCDate(),'yyyy-MM-dd hh:mm:ss '),'GMT','","rows":"',count(Distinct Entity),'"},"retults":[[getResults]]}') ) 
                       From  cteEAV)
          ,cteBld as (Select *
                            ,NewRow=IIF(Lag(Entity,1)  over (Partition By Entity Order By (Select NULL))=Entity,'',',{')
                            ,EndRow=IIF(Lead(Entity,1) over (Partition By Entity Order By (Select NULL))=Entity,',','}')
                            ,JSON=Concat('"',IIF(@ToLowerCase=1,Lower(Attribute),Attribute),'":','"',Value,'"') 
                       From  cteEAV )
    Select @JSON = @JSON+NewRow+JSON+EndRow,@Head = Head From cteBld, cteSum
    Return Replace(@Head,'[getResults]',Stuff(@JSON,1,1,''))
End
-- Parameter 1: @IncludeHead 1/0
-- Parameter 2: @ToLowerCase 1/0 (converts field name to lowercase
-- Parameter 3: (Select * From ... for XML RAW)