Bobmd Bobmd - 3 months ago 12
JSON Question

Create Coldfusion JSON Structure with Nested Arrays of Structures

In ColdFusion (REST/C, I am trying to fill a Structure called "stores". Nested inside is an array of Reports and nested inside the Reports is an array of Pics. Also nested inside Reports is an array of Questions, and nested inside the Questions is an array of Answers.

NOTE: Each Store can have several Reports, and each Report can have several Pics, and several questions, and each question can have several answers.

The final JSON should look like this...

stores:[
{"storeid":"101","storename":"STORE101",reports:[
{"reportid":"201","reportname":"REPORT201","pics":[
{"picid":"301","pictitle":"SOMEPIC1"},{"picid":"302","pictitle":"SOMEPIC2"}
],"questions":[
{"questionid":"111","question":"What is your favorite color?","answers":[
{"answerid":"555","answer":"Blue"},{"answerid":"556","answer":"Green"},{"answerid":"557","answer":"Red"}
]},{"questionid":"222","question":"What is your favorite car?","answers":[
{"answerid":"655","answer":"Ferrari"},{"answerid":"656","answer":"Mustang"},{"answerid":"657","answer":"Porsche"}
]}
]},{"reportid":"202","reportname":"REPORT202","pics":[
{"picid":"304","pictitle":"SOMEPIC4"},{"picid":"305","pictitle":"SOMEPIC5"}
],"questions":[
{"questionid":"111","question":"What is your favorite color?","answers":[
{"answerid":"555","answer":"Blue"},{"answerid":"556","answer":"Green"},{"answerid":"557","answer":"Red"}
]},{"questionid":"222","question":"What is your favorite car?","answers":[
{"answerid":"655","answer":"Ferrari"},{"answerid":"656","answer":"Mustang"},{"answerid":"657","answer":"Porsche"}
]}
]},
]},{"reportid":"203","reportname":"REPORT103","pics":[
{"picid":"307","pictitle":"SOMEPIC7"},{"picid":"308","pictitle":"SOMEPIC8"}
],"questions":[
{"questionid":"111","question":"What is your favorite color?","answers":[
{"answerid":"555","answer":"Blue"},{"answerid":"556","answer":"Green"},{"answerid":"557","answer":"Red"}
]},{"questionid":"222","question":"What is your favorite car?","answers":[
{"answerid":"655","answer":"Ferrari"},{"answerid":"656","answer":"Mustang"},{"answerid":"657","answer":"Porsche"}
]}
]},
{"storeid":"102","storename":"STORE102",reports:[
{"reportid":"201","reportname":"REPORT201","pics":[
{"picid":"301","pictitle":"SOMEPIC1"},{"picid":"302","pictitle":"SOMEPIC2"}
],"questions":[
],"questions":[
{"questionid":"111","question":"What is your favorite color?","answers":[
{"answerid":"555","answer":"Blue"},{"answerid":"556","answer":"Green"},{"answerid":"557","answer":"Red"}
]},{"questionid":"222","question":"What is your favorite car?","answers":[
{"answerid":"655","answer":"Ferrari"},{"answerid":"656","answer":"Mustang"},{"answerid":"657","answer":"Porsche"}
]}
]},{"reportid":"202","reportname":"REPORT202","pics":[
{"picid":"304","pictitle":"SOMEPIC4"},{"picid":"305","pictitle":"SOMEPIC5"}
],"questions":[
{"questionid":"111","question":"What is your favorite color?","answers":[
{"answerid":"555","answer":"Blue"},{"answerid":"556","answer":"Green"},{"answerid":"557","answer":"Red"}
]},{"questionid":"222","question":"What is your favorite car?","answers":[
{"answerid":"655","answer":"Ferrari"},{"answerid":"656","answer":"Mustang"},{"answerid":"657","answer":"Porsche"}
]}
]},
,{"reportid":"203","reportname":"REPORT103","pics":[
{"picid":"307","pictitle":"SOMEPIC7"},{"picid":"308","pictitle":"SOMEPIC8"}
],"questions":[
{"questionid":"111","question":"What is your favorite color?","answers":[
{"answerid":"555","answer":"Blue"},{"answerid":"556","answer":"Green"},{"answerid":"557","answer":"Red"}
]},{"questionid":"222","question":"What is your favorite car?","answers":[
{"answerid":"655","answer":"Ferrari"},{"answerid":"656","answer":"Mustang"},{"answerid":"657","answer":"Porsche"}
]}
]},
]},
{"storeid":"103","storename":"STORE103",reports:[
{"reportid":"201","reportname":"REPORT201","pics":[
{"picid":"301","pictitle":"SOMEPIC1"},{"picid":"302","pictitle":"SOMEPIC2"}
],"questions":[
{"questionid":"111","question":"What is your favorite color?","answers":[
{"answerid":"555","answer":"Blue"},{"answerid":"556","answer":"Green"},{"answerid":"557","answer":"Red"}
]},{"questionid":"222","question":"What is your favorite car?","answers":[
{"answerid":"655","answer":"Ferrari"},{"answerid":"656","answer":"Mustang"},{"answerid":"657","answer":"Porsche"}
]}
]},
{"reportid":"202","reportname":"REPORT202","pics":[
{"picid":"304","pictitle":"SOMEPIC4"},{"picid":"305","pictitle":"SOMEPIC5"}
],"questions":[
{"questionid":"111","question":"What is your favorite color?","answers":[
{"answerid":"555","answer":"Blue"},{"answerid":"556","answer":"Green"},{"answerid":"557","answer":"Red"}
]},{"questionid":"222","question":"What is your favorite car?","answers":[
{"answerid":"655","answer":"Ferrari"},{"answerid":"656","answer":"Mustang"},{"answerid":"657","answer":"Porsche"}
]}
]},
{"reportid":"203","reportname":"REPORT103","pics":[
{"picid":"307","pictitle":"SOMEPIC7"},{"picid":"308","pictitle":"SOMEPIC8"}
],"questions":[
{"questionid":"111","question":"What is your favorite color?","answers":[
{"answerid":"555","answer":"Blue"},{"answerid":"556","answer":"Green"},{"answerid":"557","answer":"Red"}
]},{"questionid":"222","question":"What is your favorite car?","answers":[
{"answerid":"655","answer":"Ferrari"},{"answerid":"656","answer":"Mustang"},{"answerid":"657","answer":"Porsche"}
]}
]},
]}
]


Here is the code I am failing with.

<!--- MY REPORTS (GET) --->
<cffunction name="getmyreports" access="remote" returntype="any" produces="application/json" httpmethod="get" restpath="/myreports/{user_id}/{device_id}/{token}">
<cfargument name="user_id" required="true" type="numeric" restargsource="path"/>
<cfargument name="device_id" required="true" type="string" restargsource="path"/>
<cfargument name="token" required="true" type="string" restargsource="path"/>


<cfquery name="r" datasource="#this.dsn#">
SELECT rt.report_type_id, rt.report_type, rd.user_id, u.firstname, u.lastname, u.username, rn.priority, rn.start_date, rn.aid,
rn.report_id, rn.report_name, rn.exp_date, rn.report_num,
rn.pic_num, rn.report_type, rn.report_date, rn.est_hrs,
rd.report_id, rd.report_complete, rd.report_status, rd.complete_date,
rd.report_data_id, rd.comments, rd.resubmit, s.storeid, s.client_loc, s.city, s.state
FROM report_types rt, users u, report_name rn, report_data rd, stores s
WHERE u.user_id=rd.user_id
AND rd.storeid=s.storeid
AND rt.report_type_id=rn.report_type
AND rn.report_id=rd.report_id
AND rn.report_name_status = 1
<cfif auth.usertype eq 4>AND rn.report_type = 172</cfif><!--- audit only --->
AND rd.report_status = 0 <!--- not completed ---> <!---not in (7) get rid of unassigned reports--->
AND u.user_id=#Val(auth.user_id)#
AND rn.exp_date >= #Now()#
ORDER BY s.client_loc, rn.exp_date DESC, rn.report_name
</cfquery>

<cfset stores={}>

<cfif r.recordcount>

<cfset snum=1>

<cfoutput query="r" group="client_loc">

<!--- STORES --->
<cfset stores['stores']['store[#snum#]']['storeid'] = "#storeid#">
<cfset stores['stores']['store[#snum#]']['store'] = "#client_loc# - #city#, #state#">

<cfset rnum=1>
<cfoutput>

<!--- REPORTS --->
<cfset stores['stores']['store[#snum#]']['reports']['report[#rnum#]']['report_id'] = "#report_id#">
<cfset stores['stores']['store[#snum#]']['reports']['report[#rnum#]']['report_data_id'] = "#report_data_id#">

<!--- PICS --->
<cfquery name="p" datasource="#this.dsn#">
SELECT report_id,pic_title_id,pic_title,pic_order
FROM pic_titles
WHERE report_id = #report_id#
ORDER BY pic_order
</cfquery>

<cfset pnum=1>
<cfloop query="p">
<cfset stores['stores']['store[#snum#]']['reports']['report[#rnum#]']['report_data']['pics']['pic[#pnum#]']['pic_title_id'] = "#p.pic_title_id#">
<cfset stores['stores']['store[#snum#]']['reports']['report[#rnum#]']['report_data']['pics']['pic[#pnum#]']['pic_title'] = "#p.pic_title#">
<cfset stores['stores']['store[#snum#]']['reports']['report[#rnum#]']['report_data']['pics']['pic[#pnum#]']['pic_order'] = "#p.pic_order#">
<cfset pnum=pnum+1>
</cfloop>

<!--- QUESTIONS --->
<cfquery name="q" datasource="#this.dsn#">
SELECT r.report_id,r.question_id,r.question_order,v.question,v.question_stock,v.allow_comment
FROM vendor_questions v, report_questions_chosen r
WHERE v.question_id=r.question_id
AND r.report_id = #report_id#
ORDER BY r.report_id, r.question_order
</cfquery>

<cfset qnum=1>
<cfloop query="q">
<cfset stores['stores']['store[#snum#]']['reports']['report[#rnum#]']['report_data']['questions']['q[#qnum#]']['question_id'] = "#q.question_id#">
<cfset stores['stores']['store[#snum#]']['reports']['report[#rnum#]']['report_data']['questions']['q[#qnum#]']['question'] = "#q.question#">
<cfset stores['stores']['store[#snum#]']['reports']['report[#rnum#]']['report_data']['questions']['q[#qnum#]']['question_order'] = "#q.question_order#">
<cfset stores['stores']['store[#snum#]']['reports']['report[#rnum#]']['report_data']['questions']['q[#qnum#]']['question_stock'] = "#q.question_stock#">
<cfset stores['stores']['store[#snum#]']['reports']['report[#rnum#]']['report_data']['questions']['q[#qnum#]']['allow_comment'] = "#q.allow_comment#">

<cfif q.question_stock eq "Y">
<!--- ANSWERS --->
<cfquery name="a" datasource="#this.dsn#">
SELECT a.question_id, a.answer_id, a.answer
FROM vendor_questions v, report_questions_chosen r, vendor_answers a
WHERE v.question_id=r.question_id
AND a.question_id = v.question_id
AND r.report_id = #r.report_id#
AND v.question_id = #q.question_id#
AND is_active = 1
ORDER BY question_id, answer_status
</cfquery>

<cfset anum=1>
<cfloop query="a">
<cfset stores['stores']['store[#snum#]']['reports']['report[#rnum#]']['report_data']['questions']['q[#qnum#]']['answers']['a[#anum#]']['question_id'] = "#a.question_id#">
<cfset stores['stores']['store[#snum#]']['reports']['report[#rnum#]']['report_data']['questions']['q[#qnum#]']['answers']['a[#anum#]']['answer_id'] = "#a.answer_id#">
<cfset stores['stores']['store[#snum#]']['reports']['report[#rnum#]']['report_data']['questions']['q[#qnum#]']['answers']['a[#anum#]']['answer'] = "#a.answer#">
<cfset anum=anum+1>
</cfloop>

</cfif>

<cfset qnum=qnum+1>
</cfloop>


<cfset rnum=rnum+1>

</cfoutput>

<cfset snum=snum+1>
</cfoutput>

<cfelse>

<cfset stores['stores']['error'] = "No reports">

</cfif>


<cfset mystores = serializeJSON(stores) />

<cfreturn mystores>
</cffunction>


The CFDUMP doesn't look bad, but the resulting JSON is unacceptable.

CFDUMP

Failing JSON (partial snippet). The Proper JSON would contain arrays of structures. How do I create a proper Array of Structures in CF or CFSCRIPT

For example, I shouldn't have to see "store[3]" and "report[2]" in the JSON, it should just be an array of structures with name:value pairs.

{"stores":{"store[3]":{"reports":{"report[3]":{"report_data":{"questions":{"q[1]":{"question_stock":"N","question":"Did you complete the training...


CONCLUSION
To add to the confusion, there are several queries, with several loops to acquire all the data needed to populate the arrays. I can't wrap my head around it. Thanks for any help you could provide to point me in the right direction.

Answer

I shouldn't have to see "store[3]" and "report[2]" in the JSON But that's your code! Start simple and try to understand how it works:

<cfset myStru = { stores = [] }>
<cfset myTmpStru = { storeid = 101, storename = "STORE101", reports = [ ] }>
<cfset myTmpStru2 = { reportid = 201, reportname = "REPORT201", pics = [ ] }>
<cfset ArrayAppend( myTmpStru.reports, myTmpStru2 )>
<cfset ArrayAppend( myStru.stores, myTmpStru )>
Comments