Max Min from Mysql JSON Column

I have json column in mysql and it contains gold and silver rates. What i want is to get Max Buy Rate and Max Sell Rate from multiple json records

I used below query to get max min from json

$this->model()->select('rates')->where('summary_date', $date)->max('rates->gold->buy->max');

But i am facing issue of string type in number

eg.

I have two rows. First row contain Max 9 and Second row contain Max 11. But from above query its returning me Max 9 because its considering as string and giving me wrong result. I am storing numbers in float format

rodrigo.pedra

5 years ago

Best Answer

Level 56

What does $this->model() returns?

Maybe this would do the trick:

$this->model()
    ->selectRaw("MAX(CAST(JSON_EXTRACT(`rates`, '$.gold.buy.max') AS FLOAT)) AS max")
    ->where('summary_date', $date)
    ->value('max');

Also, if you are storing the max value as a float, even inside a JSON, your first query should work.

Please or to participate in this conversation.