MySQL将json对象追加到json对象数组

本文介绍了MySQL将json对象追加到json对象数组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在此表中-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 .