arrays - 使用嵌套结构数组创建 Coldfusion JSON 结构

Question

在ColdFusion(REST/C)中，我试图填充一个名为“stores”的结构。嵌套在里面的是一个报告数组，嵌套在报告里面的是一个图片数组。嵌套在报告里面的是一个问题数组，嵌套在问题中的是答案数组。

注意:每个商店可以有多个报告，每个报告可以有多个图片，以及多个问题，每个问题可以有多个答案。

最终的 JSON 应该如下所示...

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"}
        ]}
    ]},
    ]}
]

这是我失败的代码。

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

CFDUMP 看起来不错，但生成的 JSON 是 Not Acceptable 。

CFDUMP

JSON 失败(部分片段)。正确的 JSON 将包含结构数组。如何在 CF 或 CFSCRIPT 中创建正确的结构数组

例如，我不必在 JSON 中看到“store[3]”和“report[2]”，它应该只是具有名称:值对的结构数组。

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

结论更令人困惑的是，有多个查询，其中有多个循环来获取填充数组所需的所有数据。我无法理解它。感谢您提供的任何帮助，为我指明了正确的方向。

Answer 1

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

根据评论，关于该功能的其他一些提示:

1. 我认为您正在向后接近它:) 无需跟踪数组位置。每次循环时，创建一个新的“存储”结构(“报告”等)。然后根据需要填充那个对象。完成后，将当前对象附加到正确的数组或结构中，然后忘记它。由于您一次只使用一个“商店”、“报告”等(即当前的)，因此无需担心位置或索引。

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

2. 与问题没有直接关系，但始终使用 cfqueryparam 。除了提供 SQL 注入(inject)保护之外，它还有助于在多次执行同一语句时提高查询性能，就像这里的情况一样。

3. 不要忘记本地化所有函数局部变量。这包括查询名称、循环索引变量 - 仅在函数内部使用的任何变量。使用 VAR 或 LOCAL 确定这些变量的作用域可确保它们不会泄漏到共享变量作用域中，这可能会导致奇怪且难以重现错误某些条件(只需搜索 ColdFusion + race conditions )。