MySQL 查询 JSON 列中的值并按该值排序

Question

如何根据表中字段中JSON文本的Rating平均值对查询结果进行排序：

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

我需要按照3个评分（Rating）的平均值对查询结果进行排序。这里永远只会有3个值。

目前我的查询语句如下：

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

- Kevin

---

把 JSON 放在这样的表格中并不是一个好主意。最好再加一列用于评分，然后把评分放在那里。 - Sam

注意我有三种不同的评级类型……我应该为此再创建一列，然后创建3条记录，每种类型1条记录。因为所有3种类型都是必需的。 - Kevin

你应该为评分创建一个表，并使用外键引用此表。了解更多有关规范化的信息。 - Sam

如果您的数据库绝对需要JSON，请考虑使用Postgres，它可以处理这些要求。 - Joe Love

我绝对不能改变数据库中任何表的结构，否则会完全破坏已建立的系统。显然，我也不能更改数据库系统。这样说来...那么 https://code.google.com/p/common-schema/ 呢？ - Kevin

Answer 1

对于像我这样的人，他们通过谷歌搜索并试图找到解决方案 Laravel 5.4。您可以使用->运算符提取JSON，该操作等同于json_extract函数或column->"$.key"语法。

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

看起来在2013年末会非常有用（微笑）。

- shukshin.ivan

---

你救了我的一天 :) - Deepak Singh

这是最好的Laravel方式！ - doncadavona

使用最古老的SO排序方式，此答案位于底部，但所有其他答案的得分均为0或更低...这真的需要修复并标记为被接受的答案。 - Tofandel

请注意，这不会按数字顺序排序，而是按字母数字顺序排序，例如“90”将在“100”之前，因为它以9开头：https://www.db-fiddle.com/f/c247nMub1yHmyy8Yx5jQxa/1。这是因为Laravel将在提取值之前添加`json_unquote`。即使没有`json_unquote`，MariaDB也会排序不正确。对于数字排序，可以使用`$users->orderByRaw("cast(json_extract(column,'$.key') as unsigned) desc")`。 - Fredrik Jungstedt

Answer 2

例如，field_name 的值为： {"json_field_key1":"2016/11/24","json_field_key2":"value"} 在 ORDER BY 情况下，使用以下代码获取 JSON 字段 json_field_key1 的值。

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

如果您的值是日期格式，只需修改此代码，如下所示： order by DATE(SUBSTRING_INDEX(SUBSTRING_INDEX(field_name,'json_field_key1":"',-1),'"',1)) DESC 这段代码可以将结果按日期进行排序。

- Karthikeyan Ganesan

---

谢谢。这正是我需要的。但是你知道如何将它转换为Laravel Eloquent方法吗？我不想按平均值排序，我想按最高值排序。 - Mostafa Nobaqi

仅使用 order by {json_field_name} ASC，例如 select SUBSTRING_INDEX(SUBSTRING_INDEX(field_name,'json_field_key1":"',-1),'"',1) as number_list from table order by number_list ASC || DESC。 - Karthikeyan Ganesan

Answer 3

MySQL 5.7的这个功能是否有用？http://blog.ulf-wendel.de/2013/mysql-5-7-sql-functions-for-json-udf/

- felipecao

Answer 4

在我看来，最好的解决方案是能够在应用程序中连接更新和插入事件到评论表，并将平均值计算到另一个字段中。

然后需要这些信息的查询将更容易处理，并且性能将更好。

- Victor Salvans Montesó

Answer 5

更好的解决方案是在插入之前解析数据，并将其准备好，以便在3个列或1个规范化列中使用。话虽如此，如果您正在处理不可更改的情况，并且始终具有完全相同的3个评级，则可以尝试这种方法。

ORDER BY (substring(json, 21, 1)+
substring(json, 43, 1)+
substring(json,65, 1))/3 desc;

请注意，这种解决方案是所有解决方案中最不易维护、最不灵活和最容易出错的。真正的解决方案是重构您的数据结构。

- mrras

---

我正在处理一个不可变的数据结构... 而且... 总会有3种评分类型。我稍后会尝试这个。 - Kevin

Answer 6

这并不是一件容易的事情，事实上，我甚至不确定它是否可能。

话虽如此，如果您需要在这方面访问JSON，则您的数据库结构确实不应该包含它，而是为什么不向您的数据库添加一个类型字段呢？

- ollieread

---

注意我有三种不同的评级类型...我是否应该为此再添加一列，然后创建三个记录，每种类型一个。因为这三种类型都是必需的。 - Kevin

如果你有三种类型，那么是的，你应该在数据库中有一个字段来反映这一点，无论你是否将其抽象到另一个表中完全取决于你，但你绝不能按照json字符串的内容排序，也永远不应该尝试这样做，投票反对也无法改变这个事实。 - ollieread

我想我应该在问题中说明，如果我有更改数据库结构的选择，那么是的，这将是一种可能性。也许我需要停止假设其他人总是认为每个人都是新手... 对于手头的问题来说，否定投票是完全合适的...也许你应该退后一步，处理问题本身，而不仅仅陈述你的观点...并不是说你的观点是错误的...但它并没有解决手头的问题。 - Kevin

Answer 7

评分字段可以是一个表格，其中用户、类型和值作为列，而用户是关键字。然后您可以在值列上使用mysql AVG()函数，其中关键字匹配，然后按此排序。

希望这可以帮助到您。

- ghousseyn