mysql json 字段 json_remove()

Question

我有一个 mysql json 字段(交互)，它包含如下值:

{"likes":
    [
        ['1:scott'],
        ['2:james']
    ]
}

我希望能够删除其中一个对象。像这样的东西(显然这是行不通的，因为它是一个值，而不是一个键):

update `user`
set `post` = JSON_REMOVE(interactions, '$.likes."[2:james]"')
where `id` = 3

完成后:

{"likes":
    [
        ['1:scott']
    ]
}

我整个晚上都在思考这个问题，并试图根据这里和其他地方找到的各种帖子提出解决方案，但没有运气将一些东西拼凑在一起，从而满足我的需求。感谢任何帮助，谢谢!

Answer 1

以下脚本中显示了您可以使用的选项:(根据您可能遇到性能问题的表中的行数，根据需要进行调整):

mysql> DROP TABLE IF EXISTS `user`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `user` (
    ->   `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->   `post` JSON,
    ->   `interactions` JSON
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO
    ->   `user` (`post`, `interactions`)
    -> VALUES
    ->   (
    ->     '{"likes": [["1:scott"],["3:kitty"]]}',
    ->     '{"likes": [["1:scott"],["3:kitty"]]}'
    ->   ),
    ->   (
    ->     '{"likes": [["2:james"],["1:scott"]]}',
    ->     '{"likes": [["2:james"],["1:scott"]]}'
    ->   ),
    ->   (
    ->     '{"likes": [["1:scott"],["2:james"]]}',
    ->     '{"likes": [["1:scott"],["2:james"]]}'
    ->   );
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SET @`search` := '2:james';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE `user`
    -> SET `post` = COALESCE(JSON_REMOVE(`interactions`, LEFT(
    ->     JSON_UNQUOTE(
    ->       JSON_SEARCH(`interactions`,
    ->                   'one',
    ->                   @`search`,
    ->                   NULL,
    ->                   '$.likes')
    ->     ),
    ->     CHAR_LENGTH(
    ->       JSON_UNQUOTE(
    ->         JSON_SEARCH(`interactions`,
    ->                     'one',
    ->                     @`search`,
    ->                     NULL,
    ->                     '$.likes'))) - 3)), `interactions`)
    -> -- WHERE `id` = 3
    -> ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3  Changed: 2  Warnings: 0

mysql> SELECT 
    ->   `id`,
    ->   `post`,
    ->   `interactions`
    -> FROM
    ->   `user`;
+----+---------------------------------------+---------------------------------------+
| id | post                                  | interactions                          |
+----+---------------------------------------+---------------------------------------+
|  1 | {"likes": [["1:scott"], ["3:kitty"]]} | {"likes": [["1:scott"], ["3:kitty"]]} |
|  2 | {"likes": [["1:scott"]]}              | {"likes": [["2:james"], ["1:scott"]]} |
|  3 | {"likes": [["1:scott"]]}              | {"likes": [["1:scott"], ["2:james"]]} |
+----+---------------------------------------+---------------------------------------+
3 rows in set (0.00 sec)

参见db-fiddle .