Is there a way to get the max of a JSON array in MYSQL

I've read some artciles such, Max Min from Mysql JSON Cloumn but not work

Model

has a column json named "data". This columan has a data->resposne_time value object of query for get max and min

{
	"data": {
		"csq": -62,
		"icc": "8944501801213377498",
		"imei": "869640059692007",
		"device": "modem",
		"sunset": 1652071940,
		"sunrise": 1652124780,
		"datetime": 1652082623,
		"response_time": 11.29
	},
	"message": "successful",
	"success": true
}

Tried with eloquent in tinker

$modems = LoggerModem::where('status_code', '200')->select('data')->max('data->data->response_time');
> "9.99"

But there are 55.88 on table in $model->data->data->response_time

Tried with raw on mysql shell

This works

mysql> SELECT MAX(JSON_EXTRACT(data, "$.data.response_time")) as response  FROM logger_modems WHERE status_code = '200';
+----------+
| response |
+----------+
| 55.88    |
+----------+
1 row in set (0,04 sec)

Tries on Laravel Tinker

$modems = LoggerModem::where('status_code', '200')->selectRaw("SELECT JSON_EXTRACT(data, '$.data.response_time') AS response")->value('response');
Illuminate\Database\QueryException with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT JSON_EXTRACT(data, '$.data.response_time') AS response from `logger_modem' at line 1 (SQL: select SELECT JSON_EXTRACT(data, '$.data.response_time') AS response from `logger_modems` where `status_code` = 200 limit 1)'

$modems = LoggerModem::where('status_code', '200')->selectRaw("SELECT MAX (JSON_EXTRACT(data, '$.data.response_time')) AS response")->get();
Illuminate\Database\QueryException with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT MAX(JSON_EXTRACT(data, '$.data.response_time')) AS response from `logger' at line 1 (SQL: select SELECT MAX (JSON_EXTRACT(data, '$.data.response_time')) AS response from `logger_modems` where `status_code` = 200)

$modems = LoggerModem::selectRaw("SELECT MAX(JSON_EXTRACT(data, '$.data.response_time')) AS response WHERE status_code = '200'")->value('response');
Illuminate\Database\QueryException with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT MAX(JSON_EXTRACT(data, '$.data.response_time')) AS response WHERE status_' at line 1 (SQL: select SELECT MAX(JSON_EXTRACT(data, '$.data.response_time')) AS response WHERE status_code = '200' from `logger_modems` limit 1)

Some ideas?

vincent15000

3 years ago

Best Answer

Level 63

Perhaps like this ?

use Illuminate\Support\Facades\DB;
...
$modems = DB::
	table('logger_modems')
	->selectRaw('MAX(JSON_EXTRACT(data, "$.data.response_time")) as response')
	->get();

Tell me if it helps ;).

abkrim

3 years ago

Level 13

@vincent15000 Thnaks. Is not eloquent, (you use Facade DB) but works. I've a mistake in my code.

After see your code I see that, and I've corrected.

 $modems = LoggerModem::selectRaw('MAX(JSON_EXTRACT(data, "$.data.response_time")) as response')->first()->response;
=> "55.88"

A lot of thanks

1 like

vincent15000

3 years ago

Level 63

@abkrim You are welcome.

Please or to participate in this conversation.