Skip to main content
Shraddha_Prajapati
Known Participant
May 6, 2014
Answered

QueryToJson

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=Evaluate(i)>

    </cfloop> 

  </cfloop>

    <cfreturn SerializeJSON(o)>

</cffunction>

</cfcomponent>

Product(Table)

ProductId

ProductCategoryId

ProductName


    This topic is closed to new replies. Start a new post to keep the conversation going.
    BKBK
    BKBKCorrect answer
    Community Expert
    May 6, 2014

    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>

    View reply

    10 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    May 8, 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;

    }

    Translate
    Shraddha_PrajapatiAuthor
    Known Participant
    May 12, 2014

    Thanks its work...

    Translate
    BKBK
    Community Expert
    BKBKCommunity ExpertCorrect answer
    Community Expert
    May 6, 2014

    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>

    Translate
    Steve Sommers
    Legend
    May 6, 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.

    Translate
    Steve Sommers
    Legend
    May 6, 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:

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

    Translate
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices