convert mysql result to json with correct types

mysql json functions
insert json string into mysql
mysql update json field
mysql json search array of objects
php mysql json
config json mysql
inserting json data into mysql
mysql json_set

I know how to get a mysql-row and convert it to json:

$row = mysqli_fetch_assoc(mysqli_query($db, "SELECT * FROM table WHERE id=1"));
echo json_encode($row); // it's an ajax-call

but:

the db-row has different types like int, float, string. by converting it using json_encode() all results are strings.

Is there a better way to correct the types than this:

$row['floatvalue1'] = 0+$row['floatvalue1'];
$row['floatvalue2'] = 0+$row['floatvalue2'];
$row['intvalue1'] = 0+$row['intvalue1'];

I would like to loop through the keys and add 0 because:

  • first coding rule: DRY - dont repeat yourself

but i can't because:

  • row has also other types than numbers (string, date)
  • there are many columns
  • design is in dev, so columns-names often changes

Thanks in advance and excuse my bad english :-)

EDIT (to answer the comment-question from Casimir et Hippolyte):

I call this php-code using ajax to get dynamically sql-values. in my javascript-code i use the results like this:

result['intvalue1'] += 100;

lets say the json-result of intval1 is 50, the calculated result is:

"50100", not 150


The code below is just a proof of concept. It needs encapsulation in a function/method and some polishing before using it in production (f.e. call mysqli_fetch_field() in a loop and store the objects it returns before processing any row, not once for every row).

It uses the function mysqli_fetch_field() to get information about each column of the result set and converts to numbers those columns that have numeric types. The values of MYSQLI_TYPE_* constants can be found in the documentation page of Mysqli predefined constants.

// Get the data
$result = mysqli_query($db, "SELECT * FROM table WHERE id=1");
$row    = mysqli_fetch_assoc($result);

// Fix the types    
$fixed = array();
foreach ($row as $key => $value) {
    $info = mysqli_fetch_field($result);
    if (in_array($info->type, array(
            MYSQLI_TYPE_TINY, MYSQLI_TYPE_SHORT, MYSQLI_TYPE_INT24,    
            MYSQLI_TYPE_LONG, MYSQLI_TYPE_LONGLONG,
            MYSQLI_TYPE_DECIMAL, 
            MYSQLI_TYPE_FLOAT, MYSQLI_TYPE_DOUBLE
    ))) {
        $fixed[$key] = 0 + $value;
    } else {
        $fixed[$key] = $value;
    }
}

// Compare the results
echo('all strings: '.json_encode($row)."\n");
echo('fixed types: '.json_encode($fixed)."\n");

php - convert mysql result to json with correct types, The code below is just a proof of concept. It needs encapsulation in a function/​method and some polishing before using it in production (f.e. call  Convert SQL Server results into JSON July 12, 2016 by Sifiso W. Ndlovu In my article, Warehousing JSON Formatted Data in SQL Server 2016 , we had a look at available T-SQL options for converting JSON data into rows and columns for the purposes of populating a SQL Server based data warehouse.


something like

$row['floatvalue1'] = reset( sscanf ( $row['floatvalue1']  , "%f" ));
$row['floatvalue2'] = reset( sscanf ( $row['floatvalue2']  , "%f" ));
$row['intvalue1'] = reset( sscanf ( $row['intvalue1']  , "%d" ));
json_encode($row);

MySQL 8.0 Reference Manual :: 11.5 The JSON Data Type, It returns the value's JSON type if it is valid and produces an error otherwise: Instead, the result from JSON_OBJECT() is converted to a string when assigned  Now having the PL/SQL function that converts complex object types data to JSON, you can use SQLines Data tool to transfer it into any other database. You need to edit the sqlines_tsel.txt configuration file to specify the SELECT list expression for your table, for example:


If you're simply trying to make sure that your values are operable with respect to their type, you need to first cast their type correctly.

Unless you need them server-side, I would just pass-on the json directly to the front-end and do the work there.

In Javascript, you could make an attempt at casting the numbers like so:

function tryNumber(string){

    return !isNaN( parseInt(string) ) ? parseInt(string) : string;

}

function tryDate(string){

    return !isNaN( new Date(string).getTime() ) ? new Date(string) : string;

}

tryNumber('foo'); // "hello"
tryNumber('24'); // 24
tryDate('bar'); // "bar"
tryDate('December 17, 1995'); // "Sun Dec 17 1995 00:00:00 GMT+0000 (GMT)"

These two lines attempt to cast the values as a Date/Number. If they can't be cast, they will remain String's.

MySQL: Return JSON from a standard SQL Query, mysql json. I have read about JSON objects and the JSON object type. or use one of the new MySQL supplied JSON functions here - which, ironically, appears to be even more of a hack than the hack itself! which is correct, but, let's face it, a bit of a nightmare! Same result! Converting a row to json. You can also obtain JSON values from a number of functions supplied by MySQL for this purpose (see Section 12.17.2, “Functions That Create JSON Values”) as well as by casting values of other types to the JSON type using CAST(value AS JSON) (see Converting between JSON and non-JSON values). The next several paragraphs describe how MySQL


A MySQLi OO version based on @axiac's answer, that produces a JSON array ($jsnAll) containing all records. In this code snippet, the method FixSQLType is called to fix a row. Note, it should be wrapped in a try{}catch{} block and "objMySQLi" has already been instantiated:

         $lcAllRows = array();

         // Make an SQL SELECT statement 
         $SQL = "SELECT * FROM $lcName WHERE $lcWhere";

         // Run the query
         $this->sqlResult =  $this->objMySQLi->query($SQL);

         // Fetch the result
         while( $row = $this->sqlResult->fetch_assoc()){
             $lcCount = count($lcAllRows) ;

             // Call to fix, row 
             $fixedRow = $this->FixSQLType($row);
             $lcAllRows[$lcCount]= $fixedRow;
         }

         $jsnAll = json_encode($lcAllRows);

The FixSQLType method. This is almost identical to @axiac's answer, except for the call to $this->sqlResult->fetch_field_direct($i). "fetch_field" seemed to get itself lost, using "fetch_field_direct" overcame that problem.

    private function FixSQLType($pRow){
    // FROM https://stackoverflow.com/a/28261996/7571029

    // Fix the types    
    $fixed = array();
    $i = 0;
    foreach ($pRow as $key => $value) {
        $info = $this->sqlResult->fetch_field_direct($i);
        $i++;

        if (in_array($info->type, array(
                MYSQLI_TYPE_TINY, MYSQLI_TYPE_SHORT, MYSQLI_TYPE_INT24,    
                MYSQLI_TYPE_LONG, MYSQLI_TYPE_LONGLONG,
                MYSQLI_TYPE_DECIMAL, 
                MYSQLI_TYPE_FLOAT, MYSQLI_TYPE_DOUBLE
        ))) {
            $fixed[$key] = 0 + $value;
        } else {
            $fixed[$key] = $value;
        }

    }

    return $fixed;
}

MySQL Data to JSON with PHP Fetch Array & Fetch Assoc, How to convert MySQL data objects to JSON whilst using PHP functions Fetch A Simple Example for Converting MySQL Data Result to JSON with PHP A very nice web tool to always check if your JSON is correctly The output, as explained before, has two types of access keys, an integer, and a string. MySQL 5.7 Supports JSON data types. Previous version of MySQL stores JSON datas as text file in MySQL. Previous version of MySQL stores JSON datas as text file in MySQL. We have seen clients storing the JSON files in text format inside MySQL and later they are processed by Elastic Search or some other external processing.


Convert Data from Mysql to JSON Formate using PHP, Connect to and query a wide variety of database servers including MySQL, Microsoft SQL Server, PostgreSQL, Derby, Then, write data to the JSON file format. MySQL:Export SQL to JSON – Querychat JSON is a very used data transfer format, and even used as by NoSQL databases to store information as “documents”. We will learn how to export the results of a SQL query to JSON using MySQL functions and MySQL Workbench.


Tutorial: Query a Database and Write the Data to JSON, Learn how to use and query JSON data in your MYSQL databases. MySQL version 5.7.8 introduces a JSON data type that allows you to All you need to do is add valid JSON as the field value in your insert statement. fetched from the database, it's attributes JSON is converted to an associated array. CONVERT(expr,type), CONVERT(expr USING transcoding_name) The CONVERT() function takes an expression of any type and produces a result value of the specified type. Discussion of CONVERT(expr, type) syntax here also applies to CAST(expr AS type), which is equivalent.


Working with JSON in MySQL ― Scotch.io, The CONVERT() function converts a value into the specified datatype or character set. Tip: Also look at the CAST() function. Syntax. CONVERT(value, type). OR:. Well organized and easy to understand Web building tutorials with lots of examples of how to use HTML, CSS, JavaScript, SQL, PHP, Python, Bootstrap, Java and XML.