从MySQL数据库中的JSON字符串计算值

I have a list of JSON strings held in a MySQL database.

Example;

{"external":"29.6","device":"test1","internal":"28.5"}{"external":"29.6","device":"test1","internal":"28.5"} {"external":"29.6","device":"test1","internal":"28.5"}

In PHP I am trying to calculate the average external and internal temperatures. How do I extract the values as numbers and calculate them?

Use

$arr = json_decode({"external":"29.6","device":"test1","internal":"28.5"}) 

To get array

 array (
  'external' => '29.6',
   'device' => 'test1',
   'internal' => '28.5',
 )

then use

$arr['external'] and $arr['internal']

You can use the function json_decode.

This function parse the json and return a table key/value.

$myJson = '{"external":"29.6","device":"test1","internal":"28.5"}'
$myParseJson = json_decode($myJson)

The result is:

array(3) {
  ["external"] => "29.6",
  ["device"]   => "test1",
  ["internal"] => "28.5"
}

For more information: http://php.net/manual/en/function.json-decode.php

<?php

$test1='{"external":"29.6","device":"test1","internal":"28.5"}';


$array1=json_decode($test1,true);
echo"<pre>";
print_r($array1['external']);
echo "</br>";
print_r($array1['internal']);

You need to use true in json decode otherwise you will get an stdclass object and array technics will not work.

With a little hacking, you can do this all in MySql (replace data_column with you actual column name and your_table with your table name):

select AVG(
    SUBSTRING(
        SUBSTRING(data_column, POSITION('external' IN data_column) + 11),
        1,
        POSITION("\"" IN SUBSTRING(data_column, POSITION('external' IN data_column) + 11)) - 1
    )
) as avg_external,
  AVG(
    SUBSTRING(
        SUBSTRING(data_column, POSITION('internal' IN data_column) + 11),
        1,
        POSITION("\"" IN SUBSTRING(data_column, POSITION('internal' IN data_column) + 11)) - 1
    )
) as avg_internal
from your_table;

This uses mysql string functions to search for the position of the keyword (i.e. 'external') and creates a substring starting from the end of that word. then it substrings again to extract the value to the next ".

if you have a lot of data (100k rows and more) this should be faster than fetching everything to php.

alternatively, in later MySql (5.7+) and MariaDB (10.2+) versions, you can use the JSON_EXTRACT function: https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-extract