MySQL8.0でJSONを使ってみる

lightkun

2020年11月23日 20:25

前回の記事

背景

今回はMySQLでJSONを使ってみました．扱えることは知っていたのですが，今まで触れたことがなかったので，この機会に色々試してみました．

参考書籍は，もう何度も出てきたけど一応リンク貼っときます．

公式ドキュメントはこの辺りです．

※この記事ではMySQL8.0(Docker, tag:8.0)で実行しています

JSONについて

JSON(JavaScript Object Notation)を普通に使っていますが，いつごろ作られたものなのか全然知らなかったので調べてみました．公式ドキュメントもあるみたいでこちらのようです．

JSON自体は1990年代後半ごろに登場して，2000年代前半ごろに
Douglas Crockford(ダグラス・クロックフォード)さんがJSONを広めました（githubフォローしておきました）．ではJSON開発したのは誰？と思って調べてみたのですが，よくわかりませんでした．
2000年代前半ごろに広まったということは，昔からあったのではなく最近使われるようになったんですね．
またRFC8259というのがJSON仕様の標準？になっているようです．ﾁｮｯﾄﾀﾞｹ読みました．

準備

JSONが使えるようにテーブルを作成しておきます．

CREATE TABLE json_sample (
   id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   item JSON
) DEFAULT CHARSET=utf8mb4;

レコードを登録する，結果を取得する

INSERT INTO json_sample (item) VALUES
(JSON_OBJECT('name', 'A株式会社', 'sales', 1000)),
(JSON_OBJECT('name', 'B株式会社', 'sales', 300)),
('{"name":"C株式会社", "sales":600}');

レコードの登録はJSON_OBJECT()を使うか文字列をそのままでも登録ができます．

結果の取得は他の型と同様に普通にSELECTで取り出せます．

SELECT * FROM json_sample;
+----+------------------------------------------+
| id | item                                     |
+----+------------------------------------------+
|  1 | {"name": "A株式会社", "sales": 1000}       |
|  2 | {"name": "B株式会社", "sales": 300}        |
|  3 | {"name": "C株式会社", "sales": 600}        |
+----+------------------------------------------+

(今回は見づらくなってしまいますが)JSON_PRETTY()を使えば，JSONを整形して出力することもできます．

SELECT JSON_PRETTY(item) FROM json_sample;
+------------------------------------------------+
| JSON_PRETTY(item)                              |
+------------------------------------------------+
| {
 "name": "A株式会社",
 "sales": 1000
}     |
| {
 "name": "B株式会社",
 "sales": 300
}      |
| {
 "name": "C株式会社",
 "sales": 600
}      |
+------------------------------------------------+

JSONの値を取得する

SELECT JSON_EXTRACT(item, "$.name"), JSON_EXTRACT(item, "$.sales")
FROM json_sample;
+------------------------------+-------------------------------+
| JSON_EXTRACT(item, "$.name") | JSON_EXTRACT(item, "$.sales") |
+------------------------------+-------------------------------+
| "A株式会社"                  　| 1000                         　|
| "B株式会社"                  　| 300                          　|
| "C株式会社"                  　| 600                          　|
+------------------------------+-------------------------------+

SELECT item->"$.name", item->"$.sales"
FROM json_sample;
+-----------------+-----------------+
| item->"$.name"  | item->"$.sales" |
+-----------------+-----------------+
| "A株式会社"       | 1000            |
| "B株式会社"       | 300             |
| "C株式会社"       | 600             |
+-----------------+-----------------+

値を取り出すときは，"$.値の名前"としてJSON_EXTRACT()または->を使います．ちなみに"$.値の名前"などの表記のことを公式ドキュメントではpathと呼ぶみたいです．

これで取り出せたのですが，nameに""が余計についています．これを外すには次のようにJSON_UNQUOTE(JSON_EXTRACT())を使うか->>で外すことができます．

SELECT JSON_UNQUOTE(JSON_EXTRACT(item, "$.name")), JSON_UNQUOTE(JSON_EXTRACT(item, "$.sales"))
FROM json_sample;
+--------------------------------------------+---------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(item, "$.name")) | JSON_UNQUOTE(JSON_EXTRACT(item, "$.sales")) |
+--------------------------------------------+---------------------------------------------+
| A株式会社                                    | 1000                                        |
| B株式会社                                    | 300                                         |
| C株式会社                                    | 600                                         |
+--------------------------------------------+---------------------------------------------+

SELECT item->>"$.name", item->>"$.sales"
FROM json_sample;
+-----------------+------------------+
| item->>"$.name" | item->>"$.sales" |
+-----------------+------------------+
| A株式会社         | 1000             |
| B株式会社         | 300              |
| C株式会社         | 600              |
+-----------------+------------------+

ちなみにWHEREでnameを検索してみると，""の有無に関わらず取得することができるようです（これはちょっと謎です）．

SELECT JSON_EXTRACT(item, "$.name"), JSON_EXTRACT(item, "$.sales")
FROM json_sample
WHERE item->"$.name"='A株式会社';
+------------------------------+-------------------------------+
| JSON_EXTRACT(item, "$.name") | JSON_EXTRACT(item, "$.sales") |
+------------------------------+-------------------------------+
| "A株式会社"                    | 1000                          |
+------------------------------+-------------------------------+

SELECT JSON_UNQUOTE(JSON_EXTRACT(item, "$.name")), JSON_UNQUOTE(JSON_EXTRACT(item, "$.sales"))
FROM json_sample
WHERE item->"$.name"='A株式会社';
+--------------------------------------------+---------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(item, "$.name")) | JSON_UNQUOTE(JSON_EXTRACT(item, "$.sales")) |
+--------------------------------------------+---------------------------------------------+
| A株式会社                                    | 1000                                        |
+--------------------------------------------+---------------------------------------------+

JSONのキーの配列を取得する

SELECT JSON_KEYS(item) FROM json_sample;
+-------------------+
| JSON_KEYS(item)   |
+-------------------+
| ["name", "sales"] |
| ["name", "sales"] |
| ["name", "sales"] |
+-------------------+

JSON_KEYS()を使うことでキーの配列を取り出すことができます．

（使う機会はなさそうですが）その配列の中から要素を取り出すには，先ほども使ったJSON_EXTRACT()でできました．[0]で配列の1番目の要素を指しています．

SELECT JSON_EXTRACT(JSON_KEYS(item), '$[0]') FROM json_sample;
+---------------------------------------+
| JSON_EXTRACT(JSON_KEYS(item), '$[0]') |
+---------------------------------------+
| "name"                                |
| "name"                                |
| "name"                                |
+---------------------------------------+

JSONに新しいキーと値を新規追加/上書きする

JSON_INSERT()とJSON_SET()でJSONに新しいキーと値を新規追加できます．

次のSELECT JSON_INSERT()では新規追加できているように見えますが，SELECT *としてみるとテーブルのレコードは変わっていません．これはSELECTで取得した結果をJSON_INSERT()で加工しているだけだからです．

SELECT JSON_INSERT(item, '$.new', 'json_insert')
FROM json_sample
WHERE id=1;
+----------------------------------------------------------------+
| JSON_INSERT(item, '$.new', 'json_insert')                      |
+----------------------------------------------------------------+
| {"new": "json_insert", "name": "A株式会社", "sales": 1000}       |
+----------------------------------------------------------------+

SELECT * FROM json_sample;
+----+------------------------------------------+
| id | item                                     |
+----+------------------------------------------+
|  1 | {"name": "A株式会社", "sales": 1000}       |
|  2 | {"name": "B株式会社", "sales": 300}        |
|  3 | {"name": "C株式会社", "sales": 600}        |
+----+------------------------------------------+

テーブルのレコードを変更するにはUPDATE()とJSON_INSERT()を組み合わせて使います．

UPDATE json_sample
SET item = JSON_INSERT(item, '$.new', 'json_insert')
WHERE id = 1;

SELECT * FROM json_sample;
+----+----------------------------------------------------------------+
| id | item                                                           |
+----+----------------------------------------------------------------+
|  1 | {"new": "json_insert", "name": "A株式会社", "sales": 1000}       |
|  2 | {"name": "B株式会社", "sales": 300}                              |
|  3 | {"name": "C株式会社", "sales": 600}                              |
+----+----------------------------------------------------------------+

JSON_SET()も同じように使います．

UPDATE json_sample
SET item = JSON_SET(item, '$.new', 'json_set')
WHERE id = 2;
+----+----------------------------------------------------------------+
| id | item                                                           |
+----+----------------------------------------------------------------+
|  1 | {"new": "json_insert", "name": "A株式会社", "sales": 1000}       |
|  2 | {"new": "json_set", "name": "B株式会社", "sales": 300}           |
|  3 | {"name": "C株式会社", "sales": 600}                              |
+----+----------------------------------------------------------------+

既存のJSONのキーと値を上書きするにはJSON_REPLACE()とJSON_SET()を使います．

UPDATE json_sample
SET item = JSON_REPLACE(item, '$.new', 'json_replace')
WHERE id = 1;

SELECT * FROM json_sample;
+----+-----------------------------------------------------------------+
| id | item                                                            |
+----+-----------------------------------------------------------------+
|  1 | {"new": "json_replace", "name": "A株式会社", "sales": 1000}       |
|  2 | {"new": "json_set", "name": "B株式会社", "sales": 300}            |
|  3 | {"name": "C株式会社", "sales": 600}                               |
+----+-----------------------------------------------------------------+

UPDATE json_sample
SET item = JSON_SET(item, '$.new', 'json_set2')
WHERE id = 2;

SELECT * FROM json_sample;
+----+-----------------------------------------------------------------+
| id | item                                                            |
+----+-----------------------------------------------------------------+
|  1 | {"new": "json_replace", "name": "A株式会社", "sales": 1000}       |
|  2 | {"new": "json_set2", "name": "B株式会社", "sales": 300}           |
|  3 | {"name": "C株式会社", "sales": 600}                               |
+----+-----------------------------------------------------------------+

こちらにも記載がありますが，JSON_SET()は新規追加/上書きの両方が，JSON_INSERT()は新規追加だけ，JSON_REPLACE()は上書きだけ可能です．

JSON_SET() replaces existing values and adds nonexisting values.
JSON_INSERT() inserts values without replacing existing values.
JSON_REPLACE() replaces only existing values.

JSONからデータを削除する

JSONからデータを削除するにはJSON_REMOVE()を使います．テーブルのレコードを更新したいので，ここでもUPDATE()と組み合わせて使用します．

UPDATE json_sample
SET item = JSON_REMOVE(item, '$.new')
WHERE id = 1;

SELECT * FROM json_sample;
+----+-------------------------------------------------------------+
| id | item                                                        |
+----+-------------------------------------------------------------+
|  1 | {"name": "A株式会社", "sales": 1000}                          |
|  2 | {"new": "json_set2", "name": "B株式会社", "sales": 300}       |
|  3 | {"name": "C株式会社", "sales": 600}                           |
+----+-------------------------------------------------------------+

この記事が気に入ったらサポートをしてみませんか？