How to Sort MySQL JSON Data in Laravel Eloquent Correctly

Laravel Eloquent has made accessing JSON data super easy via -> operator. And if you are not familiar with MySQL JSON, you might think that we can do sorting as easy as that too.

Bad news.

You can read up on the why this is not the case on my other article How to Sort MySQL JSON Data Correctly if you'd like more details.

Good news is, the solution is not that difficult.

Type Casting

As mentioned in my other article, in other types of columns MySQL knows exactly what data types are being stored. As such it is able to employed the right sorting algorithm efficiently. But when it comes to the JSON column, it does not. So we need to tell MySQL explicitly what it is that we are ordering by.

We can do this by using cast and orderByRaw() together as followed:

Record::query()
	->where('user_id',1)
	->orderByRaw('CAST(price->"$.discounted" AS float)', 'desc')
	->get();

Now that MySQL knows we are ordering by a float type, it will use the right algorithm to produce the expected results.

Give it a try!