在MariaDB / MySQL中的多级JSON中查询特定值

我使用MySQL 8.0测试了以下内容。根据其文档，它使用的似乎是MariaDB中存在的功能，但是我没有要测试的MariaDB实例。

SELECT * FROM mytable;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data                                                                                                                                                                                                                               |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"1": {"text": "Item1", "value": 1, "isActive": 0, "textDisplay": ""}, "2": {"text": "Item2", "value": 2, "isActive": 1, "textDisplay": ""}, "3": {"text": "Item3", "value": 3, "isActive": 1, "textDisplay": ""}, "nextValue": 4} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SELECT JSON_UNQUOTE(JSON_EXTRACT(JSON_EXTRACT(data, REPLACE(JSON_UNQUOTE(JSON_SEARCH(data, 'one', 'Item2')), '.text', '')), '$.value')) AS value FROM mytable;
+-------+
| value |
+-------+
| 2     |
+-------+

这非常脆弱，它依赖于在JSON路径上进行字符串替换，然后在进一步的JSON函数中使用这些路径。开发和维护如此复杂的SQL语句将花费您的雇主大量的工程师时间。

一种替代方法是升级到MySQL 8.0并使用JSON_TABLE（）函数将JSON文档映射到派生表中，然后可以在WHERE子句中使用条件。

SELECT j.* FROM mytable2, 
JSON_TABLE(mytable2.data, '$[*]' 
  COLUMNS (
    rowid FOR ORDINALITY,
    `text` VARCHAR(20) PATH '$.text',
    textDisplay TEXT PATH '$.textDisplay',
    value INT PATH '$.value',
    isActive BOOL PATH '$.isActive'
  )
) AS j

+-------+-------+-------------+-------+----------+
| rowid | text  | textDisplay | value | isActive |
+-------+-------+-------------+-------+----------+
|     1 | Item1 |             |     1 |        0 |
|     2 | Item2 |             |     2 |        1 |
|     3 | Item3 |             |     3 |        1 |
+-------+-------+-------------+-------+----------+

但这不适用于您拥有的JSON数据，因为该函数仅在JSON是数组的情况下才起作用，而您的数据被格式化为JSON对象。仅当将JSON数据格式更改为数组时，才使上面的示例起作用：

select * from mytable2;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data                                                                                                                                                                                                |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"text": "Item1", "value": 1, "isActive": 0, "textDisplay": ""}, {"text": "Item2", "value": 2, "isActive": 1, "textDisplay": ""}, {"text": "Item3", "value": 3, "isActive": 1, "textDisplay": ""}] |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

开发人员设计的JSON格式旨在使他们易于插入数据，但这是对JSON的完全免费使用，它使对数据的查询不必要地复杂。这是内部平台效应的一个示例：

平台内部的影响是软件架构师倾向于创建可定制的系统，以使其成为他们正在使用的软件开发平台的副本，并且通常是较差的副本。这通常效率低下，因此通常将此类系统视为反模式的示例。

正如我上面评论的那样，我会将其设计为普通的SQL表：

CREATE TABLE Items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  `text` VARCHAR(20), 
  textDisplay TEXT, 
  value INT, 
  isActive BOOL
);

在每一列中填充值：

INSERT INTO Items 
VALUES (1, 'Item1', '', 1, 0),
       (2, 'Item2', '', 2, 1),
       (3, 'Item3', '', 3, 1);

然后，您可以非常简单地查询它：

SELECT value FROM Items WHERE `text` = 'Item2';
+-------+
| value |
+-------+
|     2 |
+-------+

您的雇主应该赞成以常规方式存储数据的简便性，因为在查询数据时，它们将节省大量时间和金钱。