MySQL将json对象追加到json对象数组 [英] MySQL append json object to array of json objects

问题描述

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

推荐答案

您可以使用

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对象数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！