QueryToJson

Report · May 06, 2014

Hi i want to convert Query object into json data::

But i got this type record:

[{"PRODUCTNAME":"2014 Kia Cadenza Premium 4dr Sedan (3.3L 6cyl 6A)","PRODUCTCATEGORYID":1,"PRODUCTID":1},{"PRODUCTNAME":"2014 Chevrolet Cruze Diesel 4dr Sedan (2.0L 4cyl Turbodiesel 6A)","PRODUCTCATEGORYID":1,"PRODUCTID":2}]

And I wan to this type record::

[{"ProductName":"2014 Kia Cadenza Premium 4dr Sedan (3.3L 6cyl 6A)","ProductCategoryId":1,"ProductId":1},{"ProductName":"2014 Chevrolet Cruze Diesel 4dr Sedan (2.0L 4cyl Turbodiesel 6A)","ProductCategoryId":1,"ProductId":2}]

I want Field name according to my Table Column Name...(Not all characters of columns are capital like above)

my code:

QueryToJson.cfm

<!DOCTYPE html>

<html>

<body>

myJSON=new Component.queryTojson().queryToJSON();

</cfscript>

</body>

</html>

queryTojson.cfc

SELECT TOP 2 * from Product;

</cfquery>

</cfloop>

</cffunction>

</cfcomponent>

Product(Table)

ProductId

ProductCategoryId

ProductName

Report · May 06, 2014

Query column names and structure key names are capitalized in CF and I that is just something you have to deal with. There is one exception with structures that I am aware of:

will be capitalized when using StructKeyList() whereas

will preserve the upper/lower case. I'm not aware of any such exception with query column names.

If I have full control of the end using the json data, I send the column names through LCase() and just deal with lower case names. Other than that, the only other option is to create a hard coded field name translater: PRODUCTID--> ProductId.

Hope this helps.

Report · May 06, 2014

You could use replaceList() on the resulting JSON string. It does a case-sensitive replacement.

[{"PRODUCTNAME":"2014 Kia Cadenza Premium 4dr Sedan (3.3L 6cyl 6A)","PRODUCTCATEGORYID":1,"PRODUCTID":1},{"PRODUCTNAME":"2014 Chevrolet Cruze Diesel 4dr Sedan (2.0L 4cyl Turbodiesel 6A)","PRODUCTCATEGORYID":1,"PRODUCTID":2}]

</cfsavecontent>

<p><cfoutput>#newJsonString#</cfoutput></p>

Report · May 06, 2014

Hmmm. I've never used (or knew about) replaceList() -- learn something new everyday. I'll have to file that in my arsenal of tricks.

Report · May 06, 2014

Only this way replaceList(), i can do this , Have you any other way???

Report · May 07, 2014

Hi, i try to write queryTojson.cfc in script format:

component

{

public string function queryToJSON() output="yes"

{

q = new Query();

q.setdataSource("TestingDataSource");

q.setsql("SELECT * FROM Product");

qResult = q.execute().getresult();

o=ArrayNew(1);

i=0;

r=0;

x=0;

for(x=1;x LTE qResult.recordcount;x=x+1)

{

r=qresult.Currentrow;

var l = ListLen(qResult.Columnlist);

for (i = 1; i lte l; i = i + 1) // you also can use i++ instead

{

o=Evaluate(i);

}

jsonString = SerializeJSON(o);

return jsonString;

}

But i got the output in this format:

[{"3":3,"2":2,"1":1}]

How to write queryTojson.cfc in script format??

Report · May 07, 2014

You are getting that output because r remains fixed. The dynamic value of row number you should use in setting rows in o is x, not r. You could modify the code as follows:

public string function queryToJSON()

{

var q = new Query();

var qResult = "";

var colList = "";

var l = 0;

var x = 0;

var i = 0;

var o=ArrayNew(1);

q.setdataSource("TestingDataSource");

q.setsql("SELECT * FROM Product");

qResult = q.execute().getresult();

colList = qResult.Columnlist;

l = ListLen(colList);

for(x=1;x LTE qResult.recordcount;x=x+1) // x is row number

{

for (i = 1; i lte l; i = i + 1) // you also can use i++ instead

{

columnName = listGetAt(colList,i);

o=qResult[columnName];

}

jsonString = SerializeJSON(o);

return jsonString;

}

Anyway, why do you serialize an array representation of the result-set? You can just serialize the result-set itself directly, like this:

public string function queryToJSON()

{

var q = new Query();

var qResult = "";

q.setdataSource("TestingDataSource");

q.setsql("SELECT * FROM Product");

qResult = q.execute().getresult();

jsonString = SerializeJSON(qResult);

return jsonString;

}

Report · May 07, 2014

Yet another suggestion: use a struct instead of an array. A struct would give more information, as it can hold the column name as key. Here is the struct version of your code:

public string function queryToJSON()

{

var q = new Query();

var qResult = "";

var colList = "";

var l = 0;

var x = 0;

var i = 0;

var str=structNew();

q.setdataSource("TestingDataSource");

q.setsql("SELECT * FROM Product");

qResult = q.execute().getresult();

colList = qResult.Columnlist;

l = ListLen(colList);

for(x=1;x LTE qResult.recordcount;x=x+1) // x is row number

{

for (i = 1; i lte l; i = i + 1) // you also can use i++ instead

{

columnName = listGetAt(colList,i);

str[columnName]=qResult[columnName];

}

jsonString = SerializeJSON(str);

return jsonString;

}

Report · May 07, 2014

Hi , Thanks for your reply....

Your 1St Method, i got this type output:

[{"3":"2014 Kia Cadenza Premium 4dr Sedan (3.3L 6cyl 6A)","2":1,"1":1},{"3":"2014 Chevrolet Cruze Diesel 4dr Sedan (2.0L 4cyl Turbodiesel 6A)","2":2,"1":1}]

And Your 2nd Method, i got this type output:

{"COLUMNS":["PRODUCTID","PRODUCTCATEGORYID","PRODUCTNAME"],"DATA":[[1,1,"2014 Kia Cadenza Premium 4dr Sedan (3.3L 6cyl 6A)"],[2,1,"2014 Chevrolet Cruze Diesel 4dr Sedan (2.0L 4cyl Turbodiesel 6A)"]]}

And your 3rd method struct version , in this i got following output:

{"2":{"PRODUCTNAME":"2014 Chevrolet Cruze Diesel 4dr Sedan (2.0L 4cyl Turbodiesel 6A)","PRODUCTCATEGORYID":1,"PRODUCTID":2},"1":{"PRODUCTNAME":"2014 Kia Cadenza Premium 4dr Sedan (3.3L 6cyl 6A)","PRODUCTCATEGORYID":1,"PRODUCTID":1}}

But actually i want to get this type of json output, because its suit for kendo Model:

[{"ProductName":"2014 Kia Cadenza Premium 4dr Sedan (3.3L 6cyl 6A)","ProductCategoryId":1,"ProductId":1},{"ProductName":"2014 Chevrolet Cruze Diesel 4dr Sedan (2.0L 4cyl Turbodiesel 6A)","ProductCategoryId":1,"ProductId":2}]

Have you any other script code to get this type of json Data, from Query object???

Report · May 08, 2014

What you want is so close to the third result, it requires just a slight change in that code.

public string function queryToJSON()

{

var q = new Query();

var qResult = "";

var colList = "";

var l = 0;

var x = 0;

var i = 0;

var str=structNew();

var arr=arrayNew(1);

q.setdataSource("TestingDataSource");

q.setsql("SELECT * FROM Product");

qResult = q.execute().getresult();

colList = qResult.Columnlist;

l = ListLen(colList);

for(x=1;x LTE qResult.recordcount;x=x+1) // x is row number

{

for (i = 1; i lte l; i = i + 1) // you also can use i++ instead

{

columnName = listGetAt(colList,i);

str[columnName]=qResult[columnName];

}

arr=str; // each row x is stored in the array arr as a struct

}

jsonString = SerializeJSON(arr);

return jsonString;

}

Report · May 12, 2014

Thanks its work...

Adobe Community

QueryToJson

1 Correct answer