Arnaldo Antonio Torres Arnaldo Antonio Torres - 1 year ago 74
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


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:

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?


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 Source

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

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,'')+']'


enter image description here


CREATE FUNCTION [dbo].[udf-Str-JSON] (@IncludeHead int,@ToLowerCase int,@XML xml)
Returns varchar(max)
    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,',','}')
                       From  cteEAV )
    Select @JSON = @JSON+NewRow+JSON+EndRow,@Head = Head From cteBld, cteSum
    Return Replace(@Head,'[getResults]',Stuff(@JSON,1,1,''))
-- Parameter 1: @IncludeHead 1/0
-- Parameter 2: @ToLowerCase 1/0 (converts field name to lowercase
-- Parameter 3: (Select * From ... for XML RAW)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download