10 Replies Latest reply on May 12, 2014 1:21 AM by Shraddha Prajapati

    QueryToJson

    Shraddha Prajapati Level 1

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

          </cfloop> 

        </cfloop>

          <cfreturn SerializeJSON(o)>

      </cffunction>

      </cfcomponent>

       

       

      Product(Table)

      ProductId

      ProductCategoryId

      ProductName

       


        • 1. Re: QueryToJson
          Steve Sommers Level 4

          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.

          • 2. Re: QueryToJson
            BKBK Adobe Community Professional & MVP

            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>

            • 3. Re: QueryToJson
              Steve Sommers Level 4

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

              • 4. Re: QueryToJson
                Shraddha Prajapati Level 1

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

                • 5. Re: QueryToJson
                  Shraddha Prajapati Level 1

                  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[r][i]=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??


                  • 6. Re: QueryToJson
                    BKBK Adobe Community Professional & MVP

                    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[x][i]=qResult[columnName][x];

                       }

                    }

                    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;

                    }

                    • 7. Re: QueryToJson
                      BKBK Adobe Community Professional & MVP

                      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[x][columnName]=qResult[columnName][x];

                         }

                      }

                      jsonString = SerializeJSON(str);

                      return jsonString;

                      }

                      • 8. Re: QueryToJson
                        Shraddha Prajapati Level 1

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

                        • 9. Re: QueryToJson
                          BKBK Adobe Community Professional & MVP

                          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[x][columnName]=qResult[columnName][x];

                             }

                           

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

                          }

                          jsonString = SerializeJSON(arr);

                          return jsonString;

                          }

                          • 10. Re: QueryToJson
                            Shraddha Prajapati Level 1

                            Thanks its work...