Create Coldfusion JSON Structure with Nested Arrays of Structures

Question

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.

So wait - is Stores a structure or an array? Ie, at the top level, you want a list of stores (list being an array, right?) — Raymond Camden
– Raymond Camden, Commented Aug 18, 2016 at 21:25
Stores should be an array of structures, like this stores:[{storeid:123}] — Bobmd
– Bobmd, Commented Aug 18, 2016 at 21:44
See Bernhard's comment answer below. I think your biggest issue was not using arrayAppend to add to your array of stores. — Raymond Camden
– Raymond Camden, Commented Aug 18, 2016 at 21:48
Oh- that and not explicitly making stores an array of course. — Raymond Camden
– Raymond Camden, Commented Aug 18, 2016 at 21:48
Excellent yeah, syntax error on my part. I didn't know you could create an array in a structure like that. Pretty cool <cfset myStru = { stores = [] }> Coldfusion never ceases to amaze me. — Bobmd
– Bobmd, Commented Aug 18, 2016 at 22:01

Community · Accepted Answer · 2017-05-23 11:51:35Z

how do I loop around the results of the quer(ies) and populate the arrays without having to use store[1], store[2], etc

Based on the comments, a few other tips about the function:

I think you are approaching it backwards :) There is no need to keep track of array positions. Each time you loop, create a new "store" structure ("report", etcetera). Then populate that object as needed. When finished, append the current object to the proper array or structure and forget it. Since you are only ever working with one "store", "report", etcetera at a time (ie the current one) there is no need to worry about positions or indexes.

<cfset storeArray = []>
<cfloop ...>

    <!--- create a new store object --->
    <cfset currStore = { "reports" = [] }>
     ... populate ....

    <cfloop ....>
        <!--- create a new report object --->
        <cfset currReport = { "pics" = [], "questions" = [] }>
        ... populate ....

       <!--- add to array --->
       <cfset arrayAppend( currStore.reports, currReport )>

    </cfloop>

    <!--- add to array --->
    <cfset arrayAppend( storeArray, currStore )>

</cfloop>

Not directly related to the question, but always use cfqueryparam. Aside from providing sql injection protection, it also helps boost query performance when the same statement is executed multiple times, as is the case here.
Do not forget to localize ALL function local variables. That includes query names, loop index variables - any variables used only inside the function. Scoping those variables with VAR or LOCAL ensures they do not leak over into the shared variables scope, which can cause weird and difficult to reproduce bugs under certain conditions (just do a search on ColdFusion + race conditions).

Thank you all very much. Your suggestions worked like a charm. With your help I was able to get it working. I tried to upvote your answers but I don't have enough rep yet.

Bernhard Döbler · Accepted Answer · 2016-08-18 21:27:09Z

1

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

answered Aug 18, 2016 at 21:27

Bernhard Döbler

2,1223 gold badges29 silver badges43 bronze badges

2 Comments

Bobmd Over a year ago

Thank you Bernhard & Raymond, taking it one step further I was able to get the PICS array into the JSON, and it's looking good. My question is how do I loop around the results of the quer(ies) and populate the arrays without having to use store[1], store[2], etc. Can you provide a quick code sample of where you would place the loops & array appends? {"STORES":[{"STORENAME":"STORE101","REPORTS":[{"REPORTNAME":"REPORT201","PICS":[{"PICID":301,"PICTITLE":"PIC301"}],"REPORTID":201}],"STOREID":101}]}

Bobmd Over a year ago

@Leigh thank you. Very helpful. I am experimenting with it now.

Collectives™ on Stack Overflow

Create Coldfusion JSON Structure with Nested Arrays of Structures

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related