主页 / dba / 问题 / 293864
Accepted
Marcus
Asked: 2021-06-08 00:12:57 +0800 CST

是否可以在 MySQL 中按值删除 JSON 数组元素？

  • 772

在 MySQL 中，可以通过索引删除数组元素：

SELECT JSON_REMOVE('["a", "b", "c"]', '$[1]') `result`;
+------------+
| result     |
+------------+
| ["a", "c"] |
+------------+

是否可以按值删除 - 在这种情况下是匹配字符串的元素"b"？

根据文档，可以通过找到索引JSON_SEARCH然后将其传递给JSON_REMOVE，但是，我想知道是否有表达式/函数可以在单个函数中执行此操作。

mysql json

2 个回答

  1. Best Answer

    在撰写本文时，MySQL 没有专门的 JSON 函数来允许您按其值删除 JSON 数组元素。您必须使用任何可用的函数来提出自定义表达式来实现您的目标。根据要求，您甚至可能想要创建自定义函数。

    更具体地说，如果要求是删除第一次出现的值，那么一种方法是这样的：

    SET @value := 'b';

SELECT
  j.j AS before_remove
, JSON_REMOVE(
    j.j
  , JSON_UNQUOTE(
      JSON_SEARCH(
        j.j
      , 'one'
      , @value
      )
    )
  ) AS after_remove
FROM
  (SELECT '["a", "b", "c", "b"]') AS j (j)
;

    回报：

    before_remove after_remove
    [“a”、“b”、“c”、“b”] [“a”，“c”，“b”]

    JSON_SEARCH返回找到的值的带引号的路径（"$[1]"在这种情况下），JSON_UNQUOTE删除引号（yielding $[1]），然后可以将路径馈送到JSON_REMOVE.

    如果要删除任意出现的 value，则表达式会变得更加复杂：

    SET @value := 'b';
SET @occurrence := 2;

SELECT
  j.j AS before_remove
, JSON_REMOVE(
    j.j
  , JSON_UNQUOTE(
      JSON_EXTRACT(
        JSON_SEARCH(
          j.j
        , 'all'
        , @value
        )
      , CONCAT('$[', @occurrence - 1, ']')
      )
    )
  ) AS after_remove
FROM
  (SELECT '["a", "b", "c", "b"]') AS j (j)
;

    输出：

    before_remove after_remove
    [“a”、“b”、“c”、“b”] [“a”，“b”，“c”]

    在这种情况下，根据找到的条目数，JSON_SEARCH可能会返回 JSON 路径数组而不是单个路径。因此，在应用 之前JSON_UNQUOTE，我们需要从与所需匹配项匹配的数组中提取特定路径，我们可以使用JSON_EXTRACT. 该CONCAT表达式返回对应的路径JSON_EXTRACT（然后我们得到一个使用的路径JSON_REMOVE——是的，我知道，这有点令人困惑）。

    最后，为了能够删除所有出现的事件，您需要遍历由返回的路径数组的结果，JSON_SEARCH并按顺序运行JSON_REMOVE每个路径，或者以某种方式构建和评估包括所有路径的单个动态JSON_REMOVE表达式。无论哪种方式，您都需要在自定义创建的函数中执行此操作。

    或者，您可以尝试以基于集合的方式解决此问题，例如使用JSON_TABLE函数将路径数组 - 输出JSON_SEARCH- 转换为行集合，每行一条路径，然后在集合上使用递归 CTE，应用每条路径一个一个地得到最后的结果作为最终的 JSON。

    选项在那里，我将解决方案留给读者作为练习。我只会补充一点，上面的两个查询都可以在 db<>fiddle在线获得。

    • 2

  2. 我对上述查询有语法错误，但它确实帮助我解决了问题。这对我有用。

    UPDATE role AS r 
SET permissions = JSON_REMOVE(r.permissions, JSON_UNQUOTE(JSON_search(r.permissions, 'one', 'my_key')) ) 
WHERE r.custom = true
    • 0

相关问题