结构中的JSON_CONTAINS字符串无处不在

Question

我在 MySQL 数据库(5.7.25)中有一个 JSON 列，可以包含任何类型的 JSON 结构(所以我没有任何对所有行都通用的属性)。

我怎样才能通过 laravel 雄辩地执行一个查询，以搜索行匹配的字符串在一个属性的 JSON 列？

我试着这么做：

JSON_CONTAINS(`payload`, "key")

但是 mySQL 抛出了以下异常：

Invalid JSON text in argument 2 to function json_contains: "Invalid value." at position 0

例如，如果结构类似于：

{first_name:"John", "last_name":"Smith", "company":"Demo Company"}

我想搜索任何属性中包含单词“ Demo”的所有行(如果字符串是 json 属性值的子字符串)。

我发现的唯一工作方式如下：

JSON_SEARCH(payload, 'one', '%Demo%') IS NOT NULL

但是它似乎有点慢，所以我想知道是否有更快的方法。

Answer 1

您可以尝试使用 MySQL 5.7.9 引入的 JSON_EXTRACT() 函数。它可以提取 JSON 对象中的值，然后您可以在 WHERE 子句中使用 LIKE 运算符筛选匹配的行。

例如，以下查询将在 JSON 列“payload”中寻找任何属性中包含“Demo”的值：

SELECT * FROM your_table
WHERE JSON_EXTRACT(payload, '$.*') LIKE '%Demo%'

这会执行全表扫描，因此可能会相对较慢。在这种情况下，您可以考虑为 JSON 列添加一个全文索引。使用 FULLTEXT 索引，您可以在 WHERE 子句中使用 MATCH AGAINST() 函数进行全文搜索。

例如，使用以下命令为“payload”列添加全文索引：

ALTER TABLE your_table
ADD FULLTEXT INDEX payload_ft_idx (payload)

然后，您可以尝试以下查询：

SELECT * FROM your_table
WHERE MATCH(payload) AGAINST('Demo' IN BOOLEAN MODE);

这将执行更快的全文搜索，因为它使用索引。最后，请注意，JSON 数据类型基本上是一种文本类型，因此它们可以使用常规文本函数进行搜索。