Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
7
Help us understand the problem. What is going on with this article?
@tentatsu

mysqlのjson型のupdate、insertメモ

More than 1 year has passed since last update.

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になります。
なので↑のデファクトスタンダードを使ってがんばりましょう。

1回目は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)

おしまい。

7
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
tentatsu
好きな食べ物はうなぎといちごです。略してうなごですね。 最近はDB設計やら、システム設計やら、受託やら システムに関連するお仕事何でもやっています。 オフショアチーム持っているので、大きめのプロジェクトもどんとこい。 公開中のサービス: アイコンサーチ http://icon-search.jp/ ことさが http://cotosaga.com/
archipelago
【つくりこみファースト】みんなが活用できる技術で、クリエィティブに専念できる世界を!

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
7
Help us understand the problem. What is going on with this article?