MySQL Query Order By Value in JSON Column

user_1269091 0
Viewed 9828

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
mysql sql php json
7 Answers

For example field_name has the value like

{"json_field_key1":"2016/11/24","json_field_key2":"value"}

Use this code for get the json field json_field_key1 based value in ORDER BY case

select table.*, SUBSTRING_INDEX(SUBSTRING_INDEX(field_name,'json_field_key1":"',-1),'"',1) as json_field_name from table ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(field_name,'json_field_key1":"',-1),'"',1) DESC

If your value is in date format just modify this code like

order by DATE(SUBSTRING_INDEX(SUBSTRING_INDEX(field_name,'json_field_key1":"',-1),'"',1)) DESC

user_34626860
Related