在此表中-foo_table我有一列-foo_ids,其内容如下: [{"id": "432"}, {"id": "433"}]
In this table - foo_table i have a column - foo_ids and its content is as follows: [{"id": "432"}, {"id": "433"}]
我的问题是否有办法将新的json对象附加到此列? 因此,例如,如果我有这个新对象-{"id": "554"},我希望我的foo_ids列值成为- [{"id": "432"}, {"id": "433"}, {"id": "554"}] 任何想法,如果不存在该如何插入?如果已经存在,则追加新的json对象?
my question is there a way to append new json object to this column? so for example if i have this new object - {"id": "554"} i want my foo_ids column value to become - [{"id": "432"}, {"id": "433"}, {"id": "554"}] any idea how can i insert if nothing exists and append new json object if already exists?
推荐答案您可以使用 JSON_ARRAY_APPEND 函数如下:
You can use JSON_ARRAY_APPEND function as follows:
mysql> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 5.7.19 | +-----------+ 1 row in set (0.00 sec) mysql> DROP TABLE IF EXISTS `foo_table`; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE IF NOT EXISTS `foo_table` ( -> `id` SERIAL, -> `foo_ids` JSON -> ); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO `foo_table` (`foo_ids`) -> VALUES (NULL); Query OK, 1 row affected (0.00 sec) mysql> SELECT `id`, `foo_ids` -> FROM `foo_table`; +----+---------+ | id | foo_ids | +----+---------+ | 1 | NULL | +----+---------+ 1 row in set (0.00 sec) mysql> UPDATE `foo_table` -> SET `foo_ids` = IF( -> `foo_ids` IS NULL OR -> JSON_TYPE(`foo_ids`) != 'ARRAY', -> JSON_ARRAY(), -> `foo_ids` -> ), -> `foo_ids` = JSON_ARRAY_APPEND( -> `foo_ids`, -> '$', -> CAST('{"id": "432"}' AS JSON), -> '$', -> CAST('{"id": "433"}' AS JSON) -> ) -> WHERE `id` = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT `id`, `foo_ids` -> FROM `foo_table`; +----+--------------------------------+ | id | foo_ids | +----+--------------------------------+ | 1 | [{"id": "432"}, {"id": "433"}] | +----+--------------------------------+ 1 row in set (0.00 sec) mysql> UPDATE `foo_table` -> SET `foo_ids` = IF( -> `foo_ids` IS NULL OR -> JSON_TYPE(`foo_ids`) != 'ARRAY', -> JSON_ARRAY(), -> `foo_ids` -> ), -> `foo_ids` = JSON_ARRAY_APPEND( -> `foo_ids`, -> '$', -> CAST('{"id": "554"}' AS JSON) -> ) -> WHERE `id` = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT `id`, `foo_ids` -> FROM `foo_table`; +----+-----------------------------------------------+ | id | foo_ids | +----+-----------------------------------------------+ | 1 | [{"id": "432"}, {"id": "433"}, {"id": "554"}] | +----+-----------------------------------------------+ 1 row in set (0.00 sec)请参见 db-fiddle .
更新
还可以使用 <=> 运算符:
Can also use the <=> operator on the conditional:
mysql> UPDATE `foo_table` -> SET `foo_ids` = IF( -> JSON_TYPE(`foo_ids`) <=> 'ARRAY', -> `foo_ids`, -> JSON_ARRAY() -> ), -> `foo_ids` = JSON_ARRAY_APPEND( -> `foo_ids`, -> '$', -> CAST('{"id": "554"}' AS JSON) -> ) -> WHERE `id` = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0请参见 db-fiddle .
更多推荐
MySQL将json对象追加到json对象数组
发布评论