MySQL的JSON对象追加到JSON对象的数组

问题描述：

在这个表 - foo_table我有一列 - foo_ids，其内容如下：
[{"id": "432"}, {"id": "433"}]MySQL的JSON对象追加到JSON对象的数组

我的问题是有没有办法来新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。

UPDATE

还可以使用条件的<=>操作：

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对象的数组

相关推荐