ColdFusion JSON object vs array of objects

Question

I'm calling the smartsheet.com api and they will either return an object or an array, I'm able to process the object into a CF Query with the following code, but having trouble processing the array.

The JSON:

[
{
"id": 2070463980562308,
"name": "Sheet1",
"accessLevel": "OWNER",
"permalink": "https://app.smartsheet.com/b/home?"
},
{
"id": 2810804673243012,
"name": "Project Management",
"accessLevel": "OWNER",
"permalink": "https://app.smartsheet.com/b/home?"
},
{
"id": 3678697304680324,
"name": " - Dispatch Sheets",
"accessLevel": "OWNER",
"permalink": "https://app.smartsheet.com/b/home?"
}
]

My CF code:

<cfset jsonData = deserializeJSON(json.smartsheet.sheets.filecontent) />

<!--- Check we have records returned to us --->
<cfif arrayLen(jsonData.sheets)>
<!--- We want to provide the query with column names --->
<cfset strColType       =   '' />
<!--- To do this, we'll take the first result item... --->
<cfset sheets   =   jsonData.sheets[1] />
<!--- and get the list of keys from the structure. --->
<cfset thisKeyList      =   structKeyList(sheets) />
<!---
    We now need to provide the column data type.
    This example assumes everything is a VarChar.
    Looping over the list of keys, we'll append a
    datatype to the column type list defined earlier.
--->
<cfloop list="thisKeyList" index="listItem">
    <cfset listAppend(strColType,'varChar') />
</cfloop>

<!---
    Generate the new query, passing in the  
    column list, column type list and the data.
--->
<cfset qrySheets = queryNew(
thisKeyList,
strColType,
jsonData.sheets
) />

</cfif>

This code only works when I get an object back..not an array.

After deserializeJSON(), JS object becomes CF struct, JS array becomes CF array. Nothing tricky to it. <cfdump> and debug your code. — Henry
– Henry, Commented Jan 9, 2014 at 19:39
I understand it's not tricky.. I'm just not making the connection. — AbeS
– AbeS, Commented Jan 9, 2014 at 19:42
Ask where it didn't work for you, what's the exception, etc. — Henry
– Henry, Commented Jan 9, 2014 at 19:54

jk. · Accepted Answer · 2014-01-10 03:06:30Z

It looks like the JSON code you posted is for the array and not the object so I'll take a wild guess at what the object is returning.

The JSON object is most likely returning a sheets array inside of it which you are accessing as jsonData.sheets

The JSON array is just the array of sheets. So try this code:

<!--- default array --->
<cfset sheetArray = [] /> 

<cfset jsonData = deserializeJSON(json.smartsheet.sheets.filecontent) />

<!--- Check we have records returned to us --->

<cfif isArray(jsonData) AND arrayLen(jsonData)>
    <cfset sheetArray = jsonData />
<cfelseif isStruct(jsonData) AND stuctKeyExists(jsonData, "sheets")>
    <cfif isArray(jsonData.sheets) AND arrayLen(jsonData.sheets)>
        <cfset sheetArray  =  jsonData.sheets /> 
    </cfif>
</cfif>

<!--- Check we have records returned to us --->
<cfif arrayLen(sheetArray)>
<!--- We want to provide the query with column names --->
<cfset strColType       =   '' />
<!--- To do this, we'll take the first result item... --->
<cfset sheets   =   sheetArray[1] />
<!--- and get the list of keys from the structure. --->
<cfset thisKeyList      =   structKeyList(sheets) />
<!---
    We now need to provide the column data type.
    This example assumes everything is a VarChar.
    Looping over the list of keys, we'll append a
    datatype to the column type list defined earlier.
 --->
<cfloop list="thisKeyList" index="listItem">
    <cfset listAppend(strColType,'varChar') />
</cfloop>

<!---
    Generate the new query, passing in the  
    column list, column type list and the data.
--->
<cfset qrySheets = queryNew(
    thisKeyList,
    strColType,
    jsonData.sheets
 ) />

</cfif>

BTW, queryNew only takes two parameters: QueryNew(columnlist [, columntypelist]) so I'm not sure how that is working for you (must be ignoring the additional parameter).

http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f94.html

Collectives™ on Stack Overflow

ColdFusion JSON object vs array of objects

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related