MySQL在数组中按键搜索json值

Question

我有一个 JSON 对象数组，想要返回一个特定的节点。为了简化我的问题，假设数组看起来像这样:

[
    {"Race": "Orc", "strength": 14},
    {"Race": "Knight", "strength": 7},
    ...
]

我想知道骑士的实力。函数JSON_SEARCH , 返回路径 '$[1].Race' 和 path operator我可以获得力量。有没有办法将这两者结合起来，以便我可以执行以下操作？

SELECT someFunc(myCol,'$[*].Race','Orc','$.strength') AS strength
FROM myTable

我正在使用 MySQL 8.0.15。

Answer 1

你本质上是想申请 selection and projection到 JSON 文档的数组元素和对象字段。您需要执行类似 WHERE 子句的操作来选择数组中的“行”，然后执行类似选择其中一个字段(不是您在选择条件中使用的字段)的操作。

这些是在 SQL 中使用 WHERE 子句和列的 SELECT 列表完成的，但是用 JSON 做同样的事情并不是您可以使用 JSON_SEARCH() 和 JSON_CONTAINS() 等函数轻松完成的。

MySQL 8.0 提供的解决方案是 JSON_TABLE()将 JSON 文档转换为虚拟派生表的函数——就好像您已经定义了常规的行和列一样。如果 JSON 是您描述的格式(对象数组)，它就可以工作。

这是我通过将您的示例数据插入表中所做的演示:

create table mytable ( mycol json );

insert into mytable set mycol = '[{"Race": "Orc", "strength": 14}, {"Race": "Knight", "strength": 7}]';

SELECT j.* FROM mytable, JSON_TABLE(mycol, 
  '$[*]' COLUMNS (
    race VARCHAR(10) PATH '$.Race', 
    strength INT PATH '$.strength'
  )
) AS j;
+--------+----------+
| race   | strength |
+--------+----------+
| Orc    |       14 |
| Knight |        7 |
+--------+----------+

现在您可以执行通常使用 SELECT 查询执行的操作，例如选择和投影:

SELECT j.strength FROM mytable, JSON_TABLE(mycol, '$[*]' 
  COLUMNS (
    race VARCHAR(10) PATH '$.Race', 
    strength INT PATH '$.strength'
  )
) AS j 
WHERE j.race = 'Orc'
+----------+
| strength |
+----------+
|       14 |
+----------+

这有几个问题:

1. 您每次查询 JSON 数据时都需要执行此操作，或者创建一个 VIEW 来执行此操作。

2. 您说您不知道属性字段，但是要编写 JSON_TABLE() 查询，您必须在查询中指定要搜索和投影的属性。您不能将其用于完全未定义的数据。

我已经回答了很多关于在 MySQL 中使用 JSON 的类似问题。我观察到，当您想做这种事情时，将 JSON 文档视为表格，以便您可以将 WHERE 子句中的条件应用于 JSON 数据中的字段，然后您的所有查询都会变得更加困难。然后您开始觉得最好花几分钟来定义您的属性，这样您就可以编写更简单的查询。