我有一个mysql json字段(交互(,其中包含如下值:
{"likes":
[
['1:scott'],
['2:james']
]
}
我希望能够删除其中一个对象。像这样的东西(显然这行不通,因为它是一个值,而不是一个键(:
update `user`
set `post` = JSON_REMOVE(interactions, '$.likes."[2:james]"')
where `id` = 3
完成后:
{"likes":
[
['1:scott']
]
}
我整晚都在为此苦恼,并试图根据在这里和其他地方找到的各种帖子提出解决方案,但没有运气将一些东西拼凑在一起,准确地满足我的需求。任何帮助表示赞赏,谢谢!
以下脚本中显示了可以使用的选项:(根据表中的行数,您可能会遇到性能问题,请根据需要进行调整(:
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)
参见数据库小提琴。