coldfusion queryobject to json

Posted by davidejones

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"
        }
    ]
}

Trackback URL for this post: http://davidejones.com/blog/1724-coldfusion-queryobject-json/trackback/

Being Sociable...

  • If you like this article then please share it on your favourite social network and follow me on twitter for the latest updates

2 Responses to coldfusion queryobject to json

J

This is the solution I’ve been trying to find for days. Coldfusion’s default json serialization is very frustrating. Thanks for this excellent solution.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>