Copy link to clipboard
Copied
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>
<cfscript>
myJSON=new Component.queryTojson().queryToJSON();
</cfscript>
<cfdump var="#myJSON#" >
</body>
</html>
queryTojson.cfc
<cfcomponent>
<cffunction name="queryToJSON" returntype="string" access="public" output="yes">
<cfquery name="getallproducts" datasource="TestingDataSource">
SELECT TOP 2 * from Product;
</cfquery>
<cfset var o=ArrayNew(1)>
<cfset var i=0>
<cfset var r=0>
<cfloop query="getallproducts">
<cfset r=Currentrow>
<cfloop index="i" list="#(getallproducts.columnList)#">
<cfset o
</cfloop>
</cfloop>
<cfreturn SerializeJSON(o)>
</cffunction>
</cfcomponent>
Product(Table)
ProductId
ProductCategoryId
ProductName
You could use replaceList() on the resulting JSON string. It does a case-sensitive replacement.
<cfsavecontent variable="jsonString">
[{"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>
<cfset list1="PRODUCTNAME,PRODUCTCATEGORYID,PRODUCTID,PRODUCTNAME">
<cfset list2="ProductName,ProductCategoryId,ProductId,Product
...Copy link to clipboard
Copied
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:
<cfset variables.s.FieldName="abc" />
will be capitalized when using StructKeyList() whereas
<cfset variables.s["FieldName"]="abc" />
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.
Copy link to clipboard
Copied
You could use replaceList() on the resulting JSON string. It does a case-sensitive replacement.
<cfsavecontent variable="jsonString">
[{"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>
<cfset list1="PRODUCTNAME,PRODUCTCATEGORYID,PRODUCTID,PRODUCTNAME">
<cfset list2="ProductName,ProductCategoryId,ProductId,ProductName">
<cfset newJsonString = replaceList(jsonString,list1,list2)>
<p><cfoutput>#newJsonString#</cfoutput></p>
Copy link to clipboard
Copied
Hmmm. I've never used (or knew about) replaceList() -- learn something new everyday. I'll have to file that in my arsenal of tricks.
Copy link to clipboard
Copied
Only this way replaceList(), i can do this , Have you any other way???
Copy link to clipboard
Copied
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
}
}
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??
Copy link to clipboard
Copied
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
}
}
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;
}
Copy link to clipboard
Copied
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
}
}
jsonString = SerializeJSON(str);
return jsonString;
}
Copy link to clipboard
Copied
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???
Copy link to clipboard
Copied
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
}
arr
}
jsonString = SerializeJSON(arr);
return jsonString;
}
Copy link to clipboard
Copied
Thanks its work...