mysql - 在 MariaDB/MySQL 中以多级 JSON 查询特定值
问题描述
我正在使用一个 MariaDB 表,该表的 JSON 值存储如下:
{"nextValue":4,"1":{"text":"Item1","textDisplay":"","value":1,"isActive":0},"2":{"text":"Item2","textDisplay":"","value":2,"isActive":1},"3":{"text":"Item3","textDisplay":"","value":3,"isActive":1}}
我想要做的是构建一个查询,我可以在其中有一列作为项目,即“Item2”,并且在下一列中具有来自该 JSON 的键“值”的值。因此,如果“Item2”返回,在它旁边的列中,它将有“2”。
我已经尝试了许多可用于 MariaDB 的 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 |
+-------+
您的雇主应该支持以正常方式存储数据的简单性,因为在查询数据时它们将节省大量时间和金钱。
推荐阅读
- php - 与手动更改 php.ini 和使用 ini.set 有何不同?
- c# - PropertyChangedEventHandler 为空,即使属性发生了变化
- selenium - 如何在 extent-report.html 中添加 xml 数据(包含标签)
- ios - Alamofire 不允许直接发送对象
- node.js - 创建反应应用程序 - 某些东西已经在端口上运行
- python - Tweepy - 未写入文本文件
- css - 如何从打字稿修改sass文件中的参数值?
- r - 无法在 r 中调用 S3 类函数
- reactjs - 如何将道具传递给 inputComponent material-ui?
- haskell - 没有因使用“show”而产生 (Show a) 的实例