mysql json指定字段排序,MySQL JSON列中的按值查询顺序

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

解决方案

The better solution is to parse the data before the insert, and have it ready for you in 3 columns or in 1 normalized column.

Saying that, if you're dealing with a non-changeable situation, and have exactly 3 ratings always, you can try this

ORDER BY (substring(json, 21, 1)+

substring(json, 43, 1)+

substring(json,65, 1))/3 desc;

Please consider that this solution is the least maintainable and flexible of them all, and very bug prone. The real solution is restructuring your data.