I have a table which has json column named measurements , it is like this :

{
"area" : "100",
"rooms" : "2",
.
.
.
}

I tried to order by area but it does not return the correct result:

Home::where('status', 'active')->orderBy('measurements->area', 'asc')->get();

After that I used this one :

Home::query()
    ->where('status','active')
    ->orderByRaw('CAST(features->area AS unsigned)', 'asc')
    ->get();

but It returns error :

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 'area AS unsigned) limit 14 offset 0' at line 1

How can exactly order by json column? Please write code.

>Solution :

Try:

->orderByRaw('CAST(features->"$.area" AS unsigned)', 'asc')

or

->orderByRaw('CAST(JSON_EXTRACT(features, "$.area") AS unsigned)', 'asc')