How to build structure from cfquery?

Question

I have cffunction that should return JSON structure. There is more than 50 columns that I have to return. Instead of building my structure manually I would like to build that dynamically. So first loop through query then loop through each table column. Here is example:

     <cffunction name="getRecords" access="remote" output="true" returnformat="JSON">
            <cfargument name="userID" type="string" required="true">

            <cfset fnResults = StructNew()>

            <cfquery name="myQuery" datasource="test">
                SELECT 
                   ur_first,
                   ur_last,
                   ur_dob,
                   ur_gender,
                   ur_email,
                   ur_address,
                   ... and the rest of the columns
                FROM Users
                WHERE ur_id = <cfqueryparam value="#trim(arguments.userID)#" cfsqltype="cf_sql_char" maxlength="15">
                ORDER BY ur_createDt
            </cfquery>

            <cfset fnResults.recordcount = myQuery.recordcount>    

            <cfloop query="myQuery">
                <cfset qryRecs = StructNew()>
                <cfloop array="#myQuery.getColumnList()#" index="columnName">
                    <cfset qryRecs.'#columnName#' = URLEncodedFormat('#columnName#')>
                </cfloop>
             </cfloop>

             <cfset fnResults.data = qryRecs>

          <cfreturn fnResults>
     </cffunction>

This error I'm getting back after Ajax call:

CFML variable name cannot end with a "." character.
The variable qryRecs. ends with a "." character. You must either provide an additional structure key or delete the "." character.

Referencing to this line:

443 : <cfset qryRecs.'#columnName#' = URLEncodedFormat('#columnName#')>

I want to set column name to structure qryRecs like this:

<cfset qryRecs.ur_first = URLEncodedFormat(myQuery.ur_first)>

This way I don't have to set 50 plus columns manually. They all should be created dynamically. If anyone can help please let me know.

Answer 1

I created an ArrayCollection object that can convert a ColdFusion query to a few different JSON formats. Take a look and see if this fits your needs.

For example, this query:

<cfquery name="rs.q" datasource="cfbookclub">
    SELECT DISTINCT
        bookid,
        title,
        genre
    FROM
        books
    WHERE
        title LIKE <cfqueryparam value="%#arguments.term#%" cfsqltype="cf_sql_varchar" />
    ORDER BY
        genre, title
</cfquery>

will be converted to this JSON:

{
    "data": [
        {
            "bookid": 8,
            "genre": "Fiction",
            "title": "Apparition Man"
        },
        {
            "bookid": 2,
            "genre": "Non-fiction",
            "title": "Shopping Mart Mania"
        }
    ]
}

I'm also working on an update that adds meta data to the return message:

{
    "success": true,
    "message": "Array Collection created.",
    "meta": {
        "offset": 0,
        "pageSize": 0,
        "totalRecords": 0
    },
    "data": []
};