MySQL通过数组中的键搜索json值

我有一个 JSON 对象数组，想要返回一个特定的节点。为了简化我的问题，假设数组可能如下所示：

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

例如，我想知道骑士的实力。 功能JSON_SEARCH https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-search，返回路径'$[1].Race'并与路径运算符 https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_json-inline-path我可以获得力量。有没有办法将这两者结合起来，这样我就可以做如下的事情？

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

我正在使用 MySQL 8.0.15。

你本质上是想申请选择和投影 https://stackoverflow.com/questions/1031076/what-are-projection-and-selectionJSON 文档的数组元素和对象字段。您需要执行诸如 WHERE 子句之类的操作来选择数组中的一行，然后执行诸如选择其中一个字段（而不是您在选择标准中使用的字段）之类的操作。

这些是在 SQL 中使用 WHERE 子句和列的 SELECT 列表完成的，但使用 JSON_SEARCH() 和 JSON_CONTAINS() 等函数可以轻松完成与 JSON 相同的操作。

MySQL 8.0提供的解决方案是JSON_TABLE() https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html函数将 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 数据中的字段，然后所有查询都会变得更加困难。然后您开始觉得花几分钟来定义属性会更好，这样您就可以编写更简单的查询。