Creating Nested JSON in ColdFusion

Question

I'm trying to create a JSON dataset similar to the following:

'[{"COLUMNS":[{ "title": "NAME"}, { "title": "COUNTY"}],"DATA":[["John Doe","Fresno"],["Billy","Fresno"],["Tom","Kern"],["King Smith","Kings"]]}]'

In the past, I've only ever had to use an array of structures and then use #serializeJSON(array)# to generate simple JSON such as:

<cfset dataArray = ArrayNew(1) />
<cfoutput query="getData">
    <cfset dataStruct = StructNew() >
    <cfset dataStruct["date"] = getData.tempDate>
    <cfset dataStruct["yaxis1"] = getData.Value>
    <cfset dataStruct["label1"] = getData.Label>
</cfoutput>
<cfset ArrayAppend(dataArray,dataStruct) />
<cfoutput>#serializeJSON(dataArray)#</cfoutput>

To generate:

[{"date":"2015-01-15 00:05:00","label1":"Depth","yaxis1":"0.940"},{"date":"2015-01-15 00:10:00","label1":"Depth","yaxis1":"0.940"}]

How does one create a nested JSON dataset with both COLUMNS and DATA recordsets?

Thanks in advance for any help!

I think if you create a query object and then serialize it as JSON, it will look like that. — David Faber
– David Faber, Commented Jan 25, 2015 at 22:49
@DavidFaber: yup. Make that an answer so we can upvote you & get you some precious precious StackOverflow points. — Adam Cameron
– Adam Cameron, Commented Jan 25, 2015 at 23:21

David Faber · Accepted Answer · 2015-01-26 04:59:17Z

3

That is the format ColdFusion uses when serializing query objects into JSON. If you want to create a JSON object that looks like the following:

[{"COLUMNS":[{ "title": "NAME"}, { "title": "COUNTY"}],"DATA":[["John Doe","Fresno"],["Billy","Fresno"],["Tom","Kern"],["King Smith","Kings"]]}]

what you want to do is this:

<cfset myquery = queryNew("name,county", "CF_SQL_VARCHAR,CF_SQL_VARCHAR") /> <!--- The second parameter is optional but a good habit to get into --->
<cfset queryAddRow(myquery) />
<cfset querySetCell(myquery, "name", "John Doe") />
<cfset querySetCell(myquery, "county", "Fresno") />
<cfset queryAddRow(myquery) />
<cfset querySetCell(myquery, "name", "Billy") />
<cfset querySetCell(myquery, "county", "Fresno") />
<cfset queryAddRow(myquery) />
<cfset querySetCell(myquery, "name", "Tom") />
<cfset querySetCell(myquery, "county", "Kern") />
<cfset queryAddRow(myquery) />
<cfset querySetCell(myquery, "name", "King Smith") />
<cfset querySetCell(myquery, "county", "Kings") />

<cfset myqueryJSON = serializeJSON(myquery) />

<cfoutput>#myqueryJSON#</cfoutput>

The format is particularly useful when consuming ColdFusion web services from ColdFusion - you can deserialize the JSON object and use it as a query right away. It goes without saying that you can do this with <cfquery> as well:

<cfquery name="myquery" datasource="mydatasource">
    SELECT name, county
      FROM mytable
</cfquery>

<cfset myqueryJSON = serializeJSON(myquery) />

There are plenty of tools that can help you transform a struct or array of structs into a query if need be.

UPDATE per OP's comments

We have the following:

"COLUMNS":["NAME","COUNTY"]

That needs to be in the following format:

"COLUMNS":[{ "title": "NAME"}, { "title": "COUNTY"}]

This solution is a bit kludgy, if you ask me, and I'm sure there's a more elegant one, but it does seem to work from my tests:

<cfset column_match = REMatchNoCase('"columns":\[[^\]]+\]', myqueryJSON)[1] />
<cfset newcolumns = REReplace(column_match , '("[^"]+"(?=[,\]]))', '{"title":\1}', "All") />
<cfset myqueryJSON = replace(myqueryJSON, column_match, newcolumns) />

You can find an explanation of the regex I used in the REReplace() at this Regex 101 Demo. It seems to work for any number of columns.

edited Jan 26, 2015 at 4:59

answered Jan 26, 2015 at 0:19

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

New Dawn Fades Over a year ago

Thanks for getting back to me. I added a couple of additional queryAddRows and then brackets around the JSON output to get it into valid format.

<cfset myquery = queryNew("name,county", "CF_SQL_VARCHAR,CF_SQL_VARCHAR") /> <!--- The second parameter is optional but a good habit to get into ---> <cfset queryAddRow(myquery) /> <cfset querySetCell(myquery, "name", "John Doe") /> <cfset querySetCell(myquery, "county", "Fresno") /> <cfset queryAddRow(myquery) /> <cfset myqueryJSON = serializeJSON(myquery) /> <cfoutput>[#myqueryJSON#]</cfoutput>

New Dawn Fades Over a year ago

I spoke too soon -- this actually didn't create valid JSON when I added to my JavaScript. This is the output: [{"COLUMNS":["NAME","COUNTY"],"DATA":[["John Doe","Fresno"],["Billy","Fresno"],["Tom","Kern"],["King Smith","Kings"]]}] when it needs to be like this:

[{"COLUMNS":[{ "title": "NAME"}, { "title": "COUNTY"}],"DATA":[["John Doe","Fresno"],["Billy","Fresno"],["Tom","Kern"],["King Smith","Kings"]]}

New Dawn Fades Over a year ago

The columns still aren't in name value pairs.

David Faber Over a year ago

I see what you mean, but why do you need the columns in name/value pairs?

New Dawn Fades Over a year ago

I'm using the jQuery plugin called, 'DataTables', and the JSON recordset needs to be in this particular format in order for it to dynamically create columns. Here's the JS:

var dataObject = eval('[{"COLUMNS":[{ "title": "NAME"}, { "title": "COUNTY"}],"DATA":[["John Doe","Fresno"],["Billy","Fresno"],["Tom","Kern"],["King Smith","Kings"]]}]');$('#example').dataTable({         "data": dataObject [0].DATA,         "columns": dataObject [0].COLUMNS     });

Collectives™ on Stack Overflow

Creating Nested JSON in ColdFusion

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related