coldfusion queryobject to json

David Jones

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.

    	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" );
<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]#",'/>
    	<cfset json = left(json,len(json)-1) />
    	<cfset json = json & '},'/>
<cfset json = left(json,len(json)-1) />
<cfset json = json & ']'/>
<cfset json = json & '}'/>

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 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 are currently closed