mysqlのjson型のupdate、insertメモ

MySQLではバージョン5.7.8 以降でJSON型がサポートされました。
結構selectの仕方の記事は見かけるのだが実践的なupdate、insertについて書かれていることが
少なかったのでここにまとめてみます。

前準備

テーブルを作っておきましょう。

create table aaa (id int, a json);

id だけ入れてみると。

mysql> insert into aaa values (1,null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from aaa;
+------+------+
| id   | a    |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.00 sec)

insert

普通のJSON

普通のjson入れるにはjson_objectを使います。

JSON_OBJECT([key, val[, key, val] ...])

mysql> insert into aaa values (2, JSON_OBJECT('key1','val1', 'key2', 'val2'));
Query OK, 1 row affected (0.00 sec)

mysql> select * from aaa where id=2;
+------+----------------------------------+
| id   | a                                |
+------+----------------------------------+
|    2 | {"key1": "val1", "key2": "val2"} |
+------+----------------------------------+
1 row in set (0.01 sec)

key-valyeを奇数個にした場合はエラーが出ます。

mysql> insert into aaa values (3, JSON_OBJECT('key1','val1', 'key2', 'val2', 'aaa'));
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_OBJECT'

配列

配列を入れるにはjson_arrayを使います。
数値とか文字列とか、mysqlの関数とかごちゃごちゃ入れることが出来ます。

JSON_ARRAY([val[, val] ...])

mysql> insert into aaa values (4, JSON_ARRAY('aaa','bbb', 1, now()));
Query OK, 1 row affected (0.00 sec)
mysql> select * from aaa where id=4;
+------+-------------------------------------------------+
| id   | a                                               |
+------+-------------------------------------------------+
|    4 | ["aaa", "bbb", 1, "2020-01-22 14:40:42.000000"] |
+------+-------------------------------------------------+
1 row in set (0.00 sec)

JSON配列

JSON型の配列は配列の応用で、json_arrayの中にjson_objectを入れてあげればOK

mysql> insert into aaa values 
(5, JSON_ARRAY('aaa','bbb', 1, now(), JSON_OBJECT('key1','val1', 'key2', 'val2')));
Query OK, 1 row affected (0.00 sec)

mysql> select * from aaa where id=5;
+------+-----------------------------------------------------------------------------------+
| id   | a                                                                                 |
+------+-----------------------------------------------------------------------------------+
|    5 | ["aaa", "bbb", 1, "2020-01-22 14:42:25.000000", {"key1": "val1", "key2": "val2"}] |
+------+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

update

普通のJSON

JSONにkey-valueを追加したい場合

json_mergeを使います。

JSON_MERGE(json_doc, json_doc[, json_doc] ...)

-- どちらでもよい。
update aaa set a=json_merge(a,  json_object('key3', 'val3')) where id=2;
update aaa set a=json_merge(a, '{"key4": "val4"}') where id=2;

mysql> select * from aaa where id=2;
+------+------------------------------------------------------------------+
| id   | a                                                                |
+------+------------------------------------------------------------------+
|    2 | {"key1": "val1", "key2": "val2", "key3": "val3", "key4": "val4"} |
+------+------------------------------------------------------------------+

配列のJSON

配列にデータを追加したい場合

json_array_appendを使います。

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

pathは、$を起点に配置したい階層等を指定します。
詳しくはこちら。
今回は1階層目に入れるので$だけです。

mysql> update aaa set a=json_array_append(a, '$', 'hyoo') where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from aaa where id=4;
+------+---------------------------------------------------------+
| id   | a                                                       |
+------+---------------------------------------------------------+
|    4 | ["aaa", "bbb", 1, "2020-01-22 15:00:39.000000", "hyoo"] |
+------+---------------------------------------------------------+
1 row in set (0.00 sec)

pathを変えるとこんな事もできます。

mysql> update aaa set a=json_array_append(a, '$[3]', 'ttt') where id=4;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from aaa where id=4;
+------+------------------------------------------------------------------+
| id   | a                                                                |
+------+------------------------------------------------------------------+
|    4 | ["aaa", "bbb", 1, ["2020-01-22 15:00:39.000000", "ttt"], "hyoo"] |
+------+------------------------------------------------------------------+
1 row in set (0.00 sec)

元データがnullの場合

元データがnullだと、json_array_appendしてもnullのままです。がびーん

mysql> select * from aaa where id=1;
+------+------+
| id   | a    |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.00 sec)

mysql> update aaa set a=json_array_append(a, '$', 'hyo') where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> select * from aaa where id=1;
+------+------+
| id   | a    |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.00 sec)

なので、ifnullを使って回避するという荒業を思いついたので
業界のデファクトスタンダードにしましょう。

mysql> select * from aaa where id=1;
+------+------+
| id   | a    |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.00 sec)

mysql> update aaa set 
    -> a=ifnull(json_array_append(a, '$', 'hyo'), json_array('hyo'))
    -> where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from aaa where id=1;
+------+---------+
| id   | a       |
+------+---------+
|    1 | ["hyo"] |
+------+---------+
1 row in set (0.00 sec)

配列の中にjsonが入っている場合のupdate

例えば
{"comment": "abcdefg", "created_at": now()}
というjsonを配列で保持したい場合。
json方はdefault値を持てないので必ず最初はnullになります。
なので↑のデファクトスタンダードを使ってがんばりましょう。

１回目はfugafuga、2回目はmunya_munyaって文字列を入れたいよー。

-- 最初null
mysql> select * from aaa where id=1;
+------+------+
| id   | a    |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.01 sec)

-- fugafugaを入れる
mysql> update aaa set 
a=ifnull(
  json_array_append(a, '$', json_object('comment','munya_munya','created_at', now())), 
  json_array(json_object('comment','hogefuga','created_at', now()))
) where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from aaa where id=1;                                                                                                                                           +------+-----------------------------------------------------------------------+
| id   | a                                                                     |
+------+-----------------------------------------------------------------------+
|    1 | [{"comment": "hogefuga", "created_at": "2020-01-22 15:08:33.000000"}] |
+------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

-- munyamunyaを入れる
mysql> mysql> update aaa set 
a=ifnull(
  json_array_append(a, '$', json_object('comment','munya_munya','created_at', now())), 
  json_array(json_object('comment','hogefuga','created_at', now()))
) where id=1;

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from aaa where id=1;                                                                                                                                           +------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| id   | a                                                                                                                                             |
+------+-----------------------------------------------------------------------------------------------------------------------------------------------+
|    1 | [{"comment": "hogefuga", "created_at": "2020-01-22 15:08:33.000000"}, {"comment": "munya_munya", "created_at": "2020-01-22 15:08:48.000000"}] |
+------+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

おしまい。