MySQL按数组中的键搜索JSON值

Question

我有一个JSON对象数组，想返回一个特定的节点。为了简化我的问题，可以说数组看起来像这样：

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

例如，我想知道骑士的实力。函数JSON_SEARCH，返回路径'$[1].Race'，使用路径运算符可以得到强度。有没有办法将两者结合起来，所以我可以做下面的事情？

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

我正在使用MySQL 8.0.15。

Answer 1

本质上，您的意思是将选择和投影应用于JSON文档的数组元素和对象字段。您需要执行WHERE子句之类的操作来选择数组中的“行”，然后执行诸如选择一个字段（而不是您在选择条件中使用的字段）之类的操作。

这些是使用WHERE子句和列的SELECT-列表在SQL中完成的，但是使用JSON_SEARCH（）和JSON_CONTAINS（）之类的函数来轻松实现JSON并非易事。

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数据中的字段，那么所有查询都会变得更加困难。然后，您开始觉得花几分钟时间来定义属性会更好，因此您可以编写更简单的查询。