Coldfusion JSON

Question

Currently I'm getting this output (QueryBean):

But I want the "normal" JSON output, like this one:

[
  {
    "EventType": "active",
    "InstanceId": "6728E65C-XXXX-XXXXX",
    "CustomerId": "1000324234",
    "Name": "bilderbuchverein Hermsen"
    "Amount": 999999,
    "StartDate": "August, 01 2019 00:00:00",
    "ExpirationDate": null
  },
  {
    "EventType": "active",
    "InstanceId": "956FA492-XXXX-XXXXX",
    "Name": "Phantasialand"
    "CustomerId": "12345678999",
    "Amount": 123456789,
    "StartDate": "August, 14 2019 00:00:00",
    "ExpirationDate": null
  }
]

How can I manage to change the output format? My function has the parameter produces="application/json"

<cffunction name="listEvents" access="remote" returnType="any" produces="application/JSON" httpmethod="GET" restpath="/events">
    <cfquery datasource="hostmanager" name="plannedInstances">
    SELECT * FROM licenses
    </cfquery>
    <cfreturn plannedInstances>
</cffunction>

Answer 1

To get JSON output data as an array of structure, you can use Application-level defining. In Application.cfc by adding this.serialization.serializeQueryAs = "struct" you can get serialize JSON output data as an array of structure like below.

[
  {
    "CATEGORYID":20,
    "CATEGORYNAME":"php",
    "CATEGORYDESC":"php"
  },
  {
    "CATEGORYID":21,
    "CATEGORYNAME":"cf",
    "CATEGORYDESC":"cf"
  },
  {
    "CATEGORYID":22,
    "CATEGORYNAME":".Net",
    "CATEGORYDESC":".net"
  }
]

I have used the same code with my test table. (please see the example code result screenshot)

Also, you can refer SerializeJSON 'Additional format for query serialization' I think you can't able to handle this issue with produces="application/json"

I hope It's useful for you!

Answer 2

There are two steps to achieve your goal.

1. Create and return an array of structures instead of a query ( Which fully based on CFML side )
   

2. Use the ParseJson method in jQuery to parse the response from CF.

Here is an example of returning an array of structures in JSON format. It creates a single array, and populates it with individual structures inside the query loop. Insert the query column names as the structure key, and finally append the structure into the root array. Finally, return that array from the function.

<cffunction name="sampleCall"  access="remote" returntype="any" returnformat="JSON">

        <cfquery name="read" datasource="myDataSource">
            SELECT * FROM myTable limit 10
        </cfquery>
        <cfset myArray = [] >
        <cfloop query="read">
            <cfset myStr = {} >
            <cfset structInsert(myStr, "logid", read.logid)>
            <cfset structInsert(myStr, "log_datetime", read.log_datetime)>
            <cfset structInsert(myStr, "log_Details", read.log_Details)>
            <cfset arrayAppend(myArray,myStr)>
        </cfloop>
        <cfreturn myArray / >
    </cffunction>

Note : The example uses sample columns from my query. You can use your own column names.

If you dump the result like the image below