mysql - MySQL将json对象附加到json对象数组

在这个表-foo_table中，我有一个列-foo_ids，其内容如下：
[{"id": "432"}, {"id": "433"}]
我的问题是，有没有办法将新的json对象附加到此列中？
例如，如果我有这个新对象-{"id": "554"}我希望我的foo_ids列值变成-
[{"id": "432"}, {"id": "433"}, {"id": "554"}]
如果没有存在的话，我怎么插入新的JSON对象，如果已经存在？

最佳答案

您可以使用JSON_ARRAY_APPEND函数，如下所示：

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。
更新
也可以在条件下使用<=>运算符：

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。