MYSQL:如何查询 JSON 数组包含特定标签的位置

Question

MySQL 5.7.24

假设我有这样的 3 行:

ID (PK) | Name (VARCHAR) | Data (JSON)
--------+----------------+-------------------------------------
 1      | Admad          | [{"label":"Color", "value":"Red"}, {"label":"Age", "value":40}]
 2      | Saleem         | [{"label":"Color", "value":"Green"}, {"label":"Age", "value":37}, {"label":"Hoby", "value":"Chess"}]
 3      | Daniel         | [{"label":"Food", "value":"Grape"}, {"label":"Age", "value":47}, {"label":"State", "value":"Sel"}]

规则 #1:JSON 列是动态的。意味着不是每个人都会有相同的结构

规则#2:假设我不能修改数据结构

我的问题是，是否可以查询以便获取年龄 >= 40 的记录的 ID？在本例中为 1 和 3。

附加信息(在被指向重复之后):如果您查看我的数据，父容器是数组。如果我将数据存储为

{"Age":"40", "Color":"Red"} 

然后我可以简单地使用

Data->>'$.Age' >= 40

我目前的想法是使用存储过程来循环数组，但我希望我不必走那条路。第二种选择是使用正则表达式(我也不希望这样做)。如果您认为“JSON 搜索”是解决方案，请指出我是哪一个(或我这个菜鸟的一些示例)。该文档对于我的特定需求来说过于笼统。

Answer 1

这是一个演示:

mysql> create table letsayi (id int primary key, name varchar(255), data json);

mysql> > insert into letsayi values
-> (1, 'Admad', '[{"label":"Color", "value":"Red"}, {"label":"Age", "value":"40"}]'),
-> (2, 'Saleem', '[{"label":"Color", "value":"Green"}, {"label":"Age", "value":"37"}, {"label":"Hoby", "value":"Chess"}]');

mysql>  select id, name from letsayi 
        where json_contains(data, '{"label":"Age","value":"40"}');
+----+-------+
| id | name  |
+----+-------+
|  1 | Admad |
+----+-------+

我不得不说，这是您存储数据的最低效方式。没有办法使用索引来搜索您的数据，即使您在生成的列上使用索引也是如此。您甚至没有将整数“40”存储为整数——您将数字存储为字符串，这使得它们占用更多空间。

在不需要时在 MySQL 中使用 JSON 是个坏主意。

---

Is it still possible to query age >= 40?

不使用 JSON_CONTAINS()。该函数不像 WHERE 子句中的不等式条件。它只匹配子文档的完全相等。

要计算不等式，您必须升级到 MySQL 8.0 并使用 JSON_TABLE() .我最近回答了另一个问题:MySQL nested JSON column search and extract sub JSON

换句话说，您必须将 JSON 转换为一种格式，就好像您将其存储在传统的行和列中一样。但是您每次查询数据时都必须这样做。

如果您需要在 WHERE 子句中使用条件，最好不要使用 JSON。它只会使您的查询过于复杂。听听这个关于编程的老建议:

"Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it." — Brian Kernighan

---

how people tackle dynamically added form fields

您可以为动态表单字段创建键/值表:

CREATE TABLE keyvalue (
  user_id INT NOT NULL,
  label VARCHAR(64) NOT NULL,
  value VARCHAR(255) NOT NULL,
  PRIMARY KEY (user_id, label),
  INDEX (label)
);

然后您可以为每个用户的动态表单条目添加键/值对:

INSERT INTO keyvalue (user_id, label, value)
VALUES (123, 'Color', 'Red'),
       (123, 'Age', '40');

与真正的列相比，这在存储上仍然有点低效，因为每次输入用户数据时都会存储标签名称，并且您仍然将整数存储为字符串。但是如果用户真的被允许存储他们自己选择的任何标签，你就不能制作那些真正的列。

使用键/值表，查询 age > 40 更简单:

SELECT user_id FROM key_value
WHERE label = 'Age' AND value >= 40