代码之家 › 专栏 › 技术社区 › Stranger

如何在MySQL JSON对象数组中搜索?

json mysql

Stranger · 技术社区 · 4 年前

考虑以下JSON对象,

[
  {
    "id": 5964460916832,
    "name": "Size",
    "value": "Small",
    "position": 1,
    "product_id": 4588516409440
  },
  {
    "id": 5964460916833,
    "name": "Size",
    "value": "Medium",
    "position": 2,
    "product_id": 4588516409440
  },
  {
    "id": 5964460916834,
    "name": "Size",
    "value": "Large",
    "position": 3,
    "product_id": 4588516409440
  }
]

这是一个存在于名为 custom_attrs MySQL 8.0表中的JSON数据类型。我想搜索JSON数据以匹配同一对象中的多个字段。

例如

我想看看是否有匹配的 name “尺寸”和 value 同一对象中的“Medium”。它不应该与第一个对象的名称和第二个对象的值匹配。

虽然我们总是可以使用JSON表,但我不喜欢这样,因为它在JOIN过程中带来了复杂性。

JSON_SEARCH 支持LIKE运算符,但不能确保它是否来自同一对象 JSON_CONTAINS 支持多个字段,但不支持LIKE,如下所示,

SET @doc = CAST('[{"id":5964460916832,"name":"Size","value":"Small","position":1,"product_id":4588516409440},{"id":5964460916833,"name":"Size","value":"Medium","position":2,"product_id":4588516409440},{"id":5964460916834,"name":"Size","value":"Large","position":3,"product_id":4588516409440}]' AS JSON);
SELECT JSON_CONTAINS(@doc, '{"name":"Size", "value":"Small"}')

有没有任何方法可以获得类似JSON_CONTAINS的部分搜索功能, {"name":"Size", "value":"%sma%"}

在这方面的任何帮助都将大有帮助。

0 回复 | 直到 4 年前

Bill Karwin 4 年前

JSON_CONTAINS() 只适用于相等,而不适用于模式匹配。

这个 JSON_TABLE() 函数是一种解决方案,旨在解决您正在尝试执行的任务。但您说过不想使用它。

您可以模拟 JSON_TABLE() 使用其他功能。

select * from (
  select 
   json_unquote(json_extract(col, concat('$[',n.i,'].id'))) as `id`,
   json_unquote(json_extract(col, concat('$[',n.i,'].name'))) as `name`,
   json_unquote(json_extract(col, concat('$[',n.i,'].value'))) as `value`
  from (select @doc as col) j
  cross join (select 0 as i union select 1 union select 2 union select 3 union select 4 union select 5 ...) as n
) as t
where t.`id` is not null
order by id, `name`;

输出:

+---------------+------+--------+
| id            | name | value  |
+---------------+------+--------+
| 5964460916832 | Size | Small  |
| 5964460916833 | Size | Medium |
| 5964460916834 | Size | Large  |
+---------------+------+--------+

然后你可以很容易地添加一个条件,比如 AND value LIKE '%sma%' 。

正如您所看到的,这个查询甚至比您使用 JSON_TABLE() 。

实际上,当您以JSON格式存储数据,然后尝试使用SQL表达式和关系运算来查询它们,就好像它们是规范化的数据一样,任何解决方案都会很复杂。这是因为您实际上是在实际数据库的功能中实现一个小型数据库。这有时被称为 Inner-Platform Effect :

内部平台效应是指软件架构师倾向于创建一个可定制的系统,从而成为他们所使用的软件开发平台的复制品,而且往往是糟糕的复制品。这通常是低效的,并且这种系统通常被认为是反模式的例子。

如果您想要简单的查询,您应该将数据存储在正常的行和列中,而不是JSON中。然后,您可以使用非常普通的SQL来获得结果:

SELECT id, name, value FROM MyTable WHERE name = 'Size' AND value LIKE '%sma%';