根据 ActiveRecord 中的另一个数组查询 mysql json 数组

Question

我正在尝试查询mysql数据库。我只需要返回外部数组中存在的值：

query_data = tours.select(:orders)
=> [#<Tour:0x007fd1b0972438 orders: [223597]>, ...]

我有一个看起来像这样的外部数组：orders = [223597, 223598]

现在，我正在使用 ruby 来过滤数据，但它并不像我想要的那样高效：

query_data = query_data.find_all do |data|
data.orders.any? { |o| orders.include?(o) }
end

所以我的问题是，如何根据 orders 数组检查数据库数组，并且仅使用 SQL 返回数据库数组中存在这些 ID 的记录？

我正在尝试这样的事情，但我认为这不是正确的方法：

query_data.where("JSON_EXTRACT('tour.orders', '$[*]') IN #{orders}")

另外，它只是直接不起作用。

版本：

Ruby: 2.3.1
mysql: mysql  Ver 14.14 Distrib 5.7.29, for osx10.14 (x86_64) using  EditLine wrapper
Rails: 5

Answer 1

在SQL 查询的 WHERE 子句中使用JSON_EXTRACT()可以保证它无法使用索引。所以它将进行表扫描，这不是更有效率。

一般来说，我建议数据库中有JSON列的开发人员：

您可以在选择列表中提取 JSON 文档的一部分：

SELECT JSON_EXTRACT(tour.orders, '..expr..') FROM ...

但不要在 SQL 查询的任何其他子句中执行此操作。不在 WHERE 子句中，也不在 JOIN 表达式中，也不在 GROUP BY、ORDER BY、HAVEING 等中。有许多情况无法优化，它使您的SQL查询变得超级复杂，难以编码，难以阅读且难以维护。

相反，如果要与 JSON 数组的各个元素进行比较，则应以规范化方式存储它们，子表中每行一个项目。

例如，您可能有一个表tour_orders其中每行都有一个对tour.id和一个订单 ID 的引用。然后，您可以通过更易读的方式查询它：

query_data.where(order: orders)

在MySQL中使用JSON数据类型可以轻松插入大量数据，但会使许多类型的查询更加困难。如果您需要 SQL 表达式来访问 JSON 文档的各个元素，我建议避免使用 JSON 数据类型。