mysql - 从 Json 字段更新嵌套值

Question

考虑这个表:

DROP TABLE IF EXISTS `example`;
CREATE TABLE `example` (
  `id` int NOT NULL AUTO_INCREMENT,
  `content` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

这些行:

INSERT INTO example(content) 
VALUES (
  '[  { "date": "1617210148", "name": "John",  "status": "0" },
{ "date": "1617210148", "name": "Jack",  "status": "0" },
{ "date": "1617210148", "name": "Henry",  "status": "0" }]'
);

我想更新 key 的值 status哪里 name = Jack到 1
结果将是: { "date": "1617210148", "name": "Jack", "status": "1" }如何在 SQL 查询中使用 JSON_REPLACE() 或 JSON_SET() 执行此操作(我的目标是部分更新，因为我使用的是 MySQL 8.0.25)？

Answer 1

这非常尴尬，对于 MySQL 的 JSON 函数几乎是不可能的。
您可以使用 JSON_REPLACE() 或 JSON_SET()，但两者都要求您知道要更改的字段的路径。所以在这种情况下，我们可以看到数组元素是$[1]但是，如果您不知道这一点，则无法使用此解决方案。

mysql> select json_pretty(json_replace(content, '$[1].status', '1')) as j 
  from example\G
*************************** 1. row ***************************
j: [
  {
    "date": "1617210148",
    "name": "John",
    "status": "0"
  },
  {
    "date": "1617210148",
    "name": "Jack",
    "status": "1"
  },
  {
    "date": "1617210148",
    "name": "Henry",
    "status": "0"
  }
]

像你这样的问题以前在 Stack Overflow 上也出现过，例如 JSON update single value in MySQL table .在这种情况下的解决方案取决于您是否知道您的伪记录存在于哪个数组元素中。
您可以使用 JSON_SEARCH() 获取 JSON 元素的路径，但您只能按值搜索，而不能按键/值对进行搜索。如果“Jack”出现在其他领域，也会被发现。

mysql> select json_unquote(json_search(content, 'one', 'Jack')) as path from example;
+-----------+
| path      |
+-----------+
| $[1].name |
+-----------+

要搜索键/值对，您需要使用 JSON_TABLE() 并且需要升级到 MySQL 8.0。这不会告诉您元素的路径，它只允许您从数组中返回特定行。

mysql> select j.* from example cross join json_table(content, '$[*]' columns(
  date int unsigned path '$.date',
  name varchar(10) path '$.name',
  status int path '$.status')
) as j where name = 'Jack';
+------------+------+--------+
| date       | name | status |
+------------+------+--------+
| 1617210148 | Jack |      0 |
+------------+------+--------+

这是一个技巧:您可以提取 name字段，然后变成这些值的数组:

mysql> select json_extract(content, '$[*].name') as a from example;
+---------------------------+
| a                         |
+---------------------------+
| ["John", "Jack", "Henry"] |
+---------------------------+

然后您可以搜索该数组以获取数组位置:

mysql> select json_search(json_extract(content, '$[*].name'), 'one', 'Jack') as root from example;
+--------+
| root   |
+--------+
| "$[1]" |
+--------+

一些取消引用和添加 .status您可以获得要更新的字段的完整路径:

mysql> select concat(json_unquote(json_search(json_extract(content, '$[*].name'), 'one', 'Jack')), '.status') as path from example;
+-------------+
| path        |
+-------------+
| $[1].status |
+-------------+

现在在 JSON_SET() 调用中使用它:

mysql> select json_pretty( 
    json_set(content,
     concat(json_unquote(json_search(json_extract(content, '$[*].name'), 'one', 'Jack')), '.status'), 
     '1')) as newcontent 
  from example\G
*************************** 1. row ***************************
newcontent: [
  {
    "date": "1617210148",
    "name": "John",
    "status": "0"
  },
  {
    "date": "1617210148",
    "name": "Jack",
    "status": "1"
  },
  {
    "date": "1617210148",
    "name": "Henry",
    "status": "0"
  }
]

在 UPDATE 中使用它看起来像这样:

mysql> update example set content = json_set(content, concat(json_unquote(json_search(json_extract(content, '$[*].name'), 'one', 'Jack')), '.status'), '1');

那还有很长的路要走。现在比较一下这有多困难:

UPDATE content SET status = 1 WHERE name = 'Jack';

当您最终想要使用 SQL 表达式来搜索或更新 JSON 文档中的各个字段时，将数据存储在 JSON 文档中是一个代价高昂的错误。它增加了您为此编写的任何代码的复杂性，并且在您转移到另一个项目后需要接管您的代码维护的开发人员会诅咒您的名字。