coldfusion queryobject to json

I’ve been working with coldfusion for a little while now and recently needed to output some json from a query object and quickly. Rather than using some cfc or some other code to do it i threw this together. Which will take the field names of the coldfusion queryobject and its values and output a json string.

<cfscript>
    	pagesqry = QueryNew("id, title, content");
    
    	newRow = QueryAddRow(pagesqry);
    	QuerySetCell(pagesqry, "id", 1 );
    	QuerySetCell(pagesqry, "title", "Home Page" );
    	QuerySetCell(pagesqry, "content", "This is the homepage" );
    
    	newRow = QueryAddRow(pagesqry);
    	QuerySetCell(pagesqry, "id", 2 );
    	QuerySetCell(pagesqry, "title", "About Us" );
    	QuerySetCell(pagesqry, "content", "This is the about us page" );
</cfscript>
    
<cfset json = '{'/>
<cfset json = json & '"pages":['/>
<cfloop query="pagesqry">
    	<cfset json = json & '{'/>
    	<cfloop list="#ArrayToList(pagesqry.getColumnNames())#" index="col">
    		<cfset json = json & '"#col#":"#pagesqry[col][currentrow]#",'/>
    	</cfloop>
    	<cfset json = left(json,len(json)-1) />
    	<cfset json = json & '},'/>
</cfloop>
<cfset json = left(json,len(json)-1) />
<cfset json = json & ']'/>
<cfset json = json & '}'/>
    
<cfoutput>#json#</cfoutput>

I’ve just created a dummy queryobject for use obviously you can query the database or use a query on a query etc. This is the output it gave me, you can run it throuh jsonlint.com to see its valid.

{
        "pages": [
            {
                "id": "1",
                "title": "Home Page",
                "content": "This is the homepage"
            },
            {
                "id": "2",
                "title": "About Us",
                "content": "This is the about us page"
            }
        ]
}

Comments

Comments are currently closed