MySQL Query Order By Value in JSON Column

Question

How can I sort a query based on the average Rating in a field in my table, the field itself is JSON text, structured like:

[
 {"Type":1,"Rating":5},
 {"Type":2,"Rating":5},
 {"Type":3,"Rating":5}
]

I need my query to be sorted by the average of the 3 Ratings. There will always ever be only 3 values for this.

My current query is:

SELECT `Name`, `Town`, `Vehicle`, `Review`, `Rating`, `Pics`, `PostedOn` 
FROM `tbl_ShopReviews`
WHERE `Approved` = 1
ORDER BY `PostedOn` DESC

Current results:

Name    Town    Vehicle Review  Rating  Pics    PostedOn
Kevin   Chicopee    94 Corolla  Great stuff, very glad I brought it here    [{"Type":1,"Rating":5},{"Type":2,"Rating":5},{"Type":3,"Rating":5}]     \N

Answer 1

Just for those like me, who googles and tries to find solution for laravel 5.4. You can use -> operator to extract JSON, that is equal to json_extract function or to column->"$.key" syntax.

$users->orderBy('column->key', 'desc');

Looks like it would be very useful in late 2013 (smile).