MySQL查询按JSON列中的值排序 [英] MySQL Query Order By Value in JSON Column

问题描述

如何根据表格中字段的平均值Rating对查询进行排序，该字段本身是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}
]

我需要按3个Rating的平均值对查询进行排序.永远只有3个值.

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

我当前的查询是:

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

当前结果:

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

推荐答案

更好的解决方案是在插入之前解析数据，并在3列或1归一化列中为您准备好数据. 话说回来，如果您要处理的是不可更改的情况，并且始终始终准确地获得3个评分，则可以尝试

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.

这篇关于MySQL查询按JSON列中的值排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！