MySQL append json object to array of json objects

Question

In this table - foo_table i have a column - foo_ids and its content is as follows:
[{"id": "432"}, {"id": "433"}]

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?

Answer 1

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)

See db-fiddle.

UPDATE

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

See db-fiddle.