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

3 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.

Nich

David, the article is very insightful!

So, does it mean that you can generate any type of json structure by modifying the cf code?.

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>