Sort by Mysql JSON Field Value in Laravel

I recently used JSON Mysql field value for the first time in Laravel and hit the roadblock soon on how to order the data using a field that is a JSON property.

Since the data in the JSON property was being cast as a string data the usual approach of doing json_field->property in the eloquent orderBy method was not giving proper results.

The field property has to be casted as an appropriate data type before we can use it to order our results. In my case I wanted to cast it as an unsigned integer.

The $casts property on Eloquent model does not work for the JSON properties and the only way to do it is via using orderByRaw.

Livewire Component Library

Here is how I did it.

 

 

$pages = Page::orderByRaw("cast(meta->'$.views' as unsigned) desc")->paginate();

Here Page is my model and meta is the mysql json field and views is an json property.

Let me know in comment if you find a better way of doing this.

Amazon Shopping

Site Footer