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?
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.
