無事、システムもMySQL 8.0化が成功し、じゃあ今後、どう活用していこうと考えた時に、まずやったのが、WITH句なんですが、
他にもいっぱい機能がアップされた部分があって、その中で、今回JSONで追加された関数を主に試してみました。
今回は、以下の関数を試してみたいと思います
こんなサンプルを使って説明します
- テストテーブルを作成
DROP TABLE artist_json ; CREATE TABLE `artist_json` ( `id` bigint NOT NULL, `ext` json DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ; DROP TABLE artist_master ; CREATE TABLE `artist_master` ( `id` bigint NOT NULL, `created_at` datetime , `artist_id` varchar(100) DEFAULT NULL, `artist_name` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ; INSERT INTO artist_master ( id, created_at, artist_id, artist_name ) SELECT id, createdat, UUID, Name FROM artist_json ;
- サンプルデータを入れてみる
今回は、最初にJSONではない通常のカラムテーブルにデータをインサートしております。 内容は、以下の通りです
> SELECT * FROM artist_master LIMIT 10 ; +-------------+---------------------+----------------------------------+-------------------+ | id | created_at | artist_id | artist_name | +-------------+---------------------+----------------------------------+-------------------+ | 3984930787 | 2017-06-15 13:53:07 | EB29231EEEZB4519A6983D2F679B4E7B | Gardy Girault | | 4321349201 | 2017-10-17 06:42:55 | B88755A482WE457AAA4C8C2C3503648B | Mirabilis | | 5467325288 | 2018-01-04 07:27:48 | 0581D15906Q04EB5A098AB32453E58A0 | Victoria Duffield | | 6481513155 | 2018-02-01 15:38:54 | 5276D1CB212841C89C443296982660FD | The Agrestix | | 6781319503 | 2017-10-27 05:48:41 | 317DA10290984C3E99D5EFF2FDF625F8 | Jay Graydon | | 11653000245 | 2017-06-09 21:14:16 | 6120381CF51C43DBA8A4218DC8398479 | Donna Summer | | 15578885805 | 2018-01-22 10:57:54 | 489D65C7239C4AF5894F7DE50C5BC416 | Trigger Hippy | | 16817744027 | 2017-06-23 06:02:13 | 186EBB245EA0Q8959D7BA6C2267DCC2A | Booyah Riot | | 18623402157 | 2017-06-14 17:17:12 | CEEBBE9B4C474AB1B38C419363092A74 | Van Canto | | 20818126104 | 2017-10-27 05:48:41 | 17425BB584C1401A8CD484AB09139F5E | Bronk | +-------------+---------------------+----------------------------------+-------------------+
JSON_OBJECT 関数
通常のカラムをJSON形式に変換する関数です。これはMySQL8で新規に実装されたものではなく、MySQL5.7からすでに実装されております
JSON_OBJECT 関数を使って、jsonテーブルを作成する
上記のartist_masterを元にして、artist_jsonにも値を挿入します。extカラムがjson形式になっております。ちなみにですが、やっていることに全く意味はないので（笑）その点は、ご理解くださいませ
mysql> INSERT INTO artist_json ( id, ext ) SELECT id, JSON_OBJECT( "created_at",created_at, "artist_id",artist_id, "artist_name",artist_name ) AS json FROM artist_master ;
JSON_OBJECT関数は、JSON_OBJECT(key名、value名）という形式で書きます 上記のように列挙して書くことも可能です。
内容を確認します
上記のデータのどのように書き込まれたか見てみましょう。ちゃんとJSON形式になってますね（笑）
mysql> SELECT * FROM artist_json limit 5 ;
+------------+-----------------------------------------------------------------------------------------------------------------------------------+
| id | ext |
+------------+-----------------------------------------------------------------------------------------------------------------------------------+
| 3984920787 | {"artist_id": "EB29231EEE1B4519A6983D2F679B4E7B", "created_at": "2017-06-15 13:53:07.000000", "artist_name": "Gardy Girault"} |
| 4321849201 | {"artist_id": "B88755A4829E457AAA4C8C2C3503648B", "created_at": "2017-10-17 06:42:55.000000", "artist_name": "Mirabilis"} |
| 5467625288 | {"artist_id": "0581D15906404EB5A098AB32453E58A0", "created_at": "2018-01-04 07:27:48.000000", "artist_name": "Victoria Duffield"} |
| 6481503155 | {"artist_id": "5276D1CB219841C89C443296982660FD", "created_at": "2018-02-01 15:38:54.000000", "artist_name": "The Agrestix"} |
| 6781359503 | {"artist_id": "317DA10290984C3E99D5EFF2FDF625F8", "created_at": "2017-10-27 05:48:41.000000", "artist_name": "Jay Graydon"} |
+------------+-----------------------------------------------------------------------------------------------------------------------------------+
では、このデータをもとに色々関数を試してみます
JSON_PRETTY 関数
簡単にいうと、JSONを見やすくする関数です。 MySQL8で新規に実装されました。今回の例は、要素の数が少ないのであまり恩恵をうけれませんが、要素数が多いテーブルは、非常に助かる機能でしょう
> SELECT JSON_PRETTY(ext) AS j FROM artist_json limit 2 ; +---------------------------------------------------------------------------------------------------------------------------------------+ | j | +---------------------------------------------------------------------------------------------------------------------------------------+ | { "artist_id": "EB29231EEE1B4519A6983D2F679B4E7B", "created_at": "2017-06-15 13:53:07.000000", "artist_name": "Gardy Girault" } | | { "artist_id": "B88755A4829E457AAA4C8C2C3503648B", "created_at": "2017-10-17 06:42:55.000000", "artist_name": "Mirabilis" } | +---------------------------------------------------------------------------------------------------------------------------------------+
要素の抽出
まず、基本に戻って、jsonの特定要素の抽出についてまとめます。
サンプル
以下のjsonデータを、@jという変数に格納しております
SET @j = '{"name": "panda", "type": "X", "memo": {"a": "1", "b": "2", "c": "3"}, "d": 10000}';
nameの値を抽出
SELECT JSON_UNQUOTE(JSON_EXTRACT(@j,'$.name')) ; +-----------------------------------------+ | JSON_UNQUOTE(JSON_EXTRACT(@j,'$.name')) | +-----------------------------------------+ | panda | +-----------------------------------------+
memoの抽出
SELECT JSON_UNQUOTE(JSON_EXTRACT(@j,'$.memo')) ;
+-----------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(@j,'$.memo')) |
+-----------------------------------------+
| {"a": "1", "b": "2", "c": "3"} |
+-----------------------------------------+
memoのaのみ抽出
SELECT JSON_UNQUOTE(JSON_EXTRACT(@j,'$.memo.a')) ; +-------------------------------------------+ | JSON_UNQUOTE(JSON_EXTRACT(@j,'$.memo.a')) | +-------------------------------------------+ | 1 | +-------------------------------------------+
存在しないカラムを指定した場合
この場合、NULLが返されます
SELECT JSON_UNQUOTE(JSON_EXTRACT(@j,'$.memo.abc')) ; +---------------------------------------------+ | JSON_UNQUOTE(JSON_EXTRACT(@j,'$.memo.abc')) | +---------------------------------------------+ | NULL | +---------------------------------------------+
->> （ショートカット記号）関数
jsonから特定の要素を抜き出す際に利用します。 JSON_EXTRACT() と JSON_UNQUOTE() のショートカットキーになります
ショートカットを使わない記述方法
JSON_EXTRACT() 特定の要素を出す
->>を利用しない場合、以下のような書き方になります。これが本来の書き方です
mysql> SELECT JSON_EXTRACT(ext,'$.artist_name') FROM artist_json LIMIT 5 ; +-----------------------------------+ | JSON_EXTRACT(ext,'$.artist_name') | +-----------------------------------+ | "Gardy Girault" | | "Mirabilis" | | "Victoria Duffield" | | "The Agrestix" | | "Jay Graydon" | +-----------------------------------+
JSON_UNQUOTE() クオートを除去する
通常、これは単体で使うことはないはないと思いますので、上記のJSON_EXTRACTを併せて使います
mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT(ext,'$.artist_name')) FROM artist_json LIMIT 5 ; +-------------------------------------------------+ | JSON_UNQUOTE(JSON_EXTRACT(ext,'$.artist_name')) | +-------------------------------------------------+ | Gardy Girault | | Mirabilis | | Victoria Duffield | | The Agrestix | | Jay Graydon | +-------------------------------------------------+
ダブルクォーテーションが除去された、シンプルな値だけが出力されているのがわかるかと思います
ショートカット-> (->> じゃないほう)
上記を踏まえて、->は、つまり、JSON_EXTRACTのショートカットになります。 これは、MySQL 5.7ですでに実装されております
mysql> SELECT ext->'$.artist_name' FROM artist_json LIMIT 5 ; +----------------------+ | ext->'$.artist_name' | +----------------------+ | "Gardy Girault" | | "Mirabilis" | | "Victoria Duffield" | | "The Agrestix" | | "Jay Graydon" | +----------------------+
ショートカット ->>
こちらは、MySQL 8.0で新規に実装されたショートカットです。JSON_EXTRACTとJSON_UNQUOTEをやってくれるので、構文がとてもシンプルです。
mysql> SELECT ext->>'$.artist_name' FROM artist_json LIMIT 5 ; +-----------------------+ | ext->>'$.artist_name' | +-----------------------+ | Gardy Girault | | Mirabilis | | Victoria Duffield | | The Agrestix | | Jay Graydon | +-----------------------+
ショートカットの例外
ただし、下記のように変数での定義に対しては、ショートカットとして使えませんのでご注意ください
これは不可
SET @j = '{"name": "panda", "type": "X", "memo": {"a": "1", "b": "2", "c": "3"}, "d": 10000}'; SELECT @j->>'$.name' ;
これはOK
SELECT JSON_UNQUOTE(JSON_EXTRACT(@j,'$.name')) ;
JSON_OBJECTAGG
MySQL8.0にて新しく実装されました。これ。。。どう活用するのか自分はよくわからないのですが結果を見た限りでは、KEY - VALUE形式で集計しているように見えます。イメージ的に、GROUP_CONCAT 関数に似ているのかもしれません
mysql> SELECT JSON_PRETTY(JSON_OBJECTAGG(id,artist_name)) as json FROM artist_master WHERE artist_name LIKE '%Justin Bieber%' ; json ----- { "4205096328421857": "Justin Bieber & BloodPop®", "170383473237933783": "Skrillex, Justin Bieber & Diplo", "4390914644318107320": "Justin Bieber & Mariah Carey", "52998045714223230208": "Sean Kingston, Justin Bieber", "6064565074723235634": "Justin Bieber", "8906869936323230312": "Justin Bieber & Sean Kingston" }
JSON_ARRAYAGG
特定のカラムをJSON形式でまとめてくれます。ちょっと私の例が悪いのですが、おそらくGROUP BY を伴って、列挙する形になるのかな？って思います
SELECT JSON_PRETTY(JSON_ARRAYAGG(artist_name)) as json FROM artist_master WHERE artist_name LIKE '%Justin Bieber%' ; ----- [ "Justin Bieber & BloodPop®", "Skrillex, Justin Bieber & Diplo", "Justin Bieber & Mariah Carey", "Sean Kingston, Justin Bieber", "Justin Bieber", "Justin Bieber & Sean Kingston" ]
JSON_TABLE
読んで字のごとく、JSONをテーブルのように見せます。以下の例では、tmpがテンポラリテーブルみたいなイメージになりますね。
SELECT * FROM JSON_TABLE( '[{"id": 12345678, "artist_name":"Justin Bieber"}, {"id":22222222, "artist_name":"Sean Kingston"}, {"id":3333333, "artist_name":"Mariah Carey"}]', "$[*]" COLUMNS( id INT PATH "$.id", artist TEXT PATH "$.artist_name" ) ) AS tmp; +----------+---------------+ | id | artist | +----------+---------------+ | 12345678 | Justin Bieber | | 22222222 | Sean Kingston | | 3333333 | Mariah Carey | +----------+---------------+
JSON_TABLEの応用
JSON_TABLEとCTEを応用して、通常のテーブルとJOINする例です
WITH tmp_artist AS
(
SELECT * FROM JSON_TABLE(
'[{"id": 12345678, "artist_name":"Justin Bieber"}, {"id":22222222, "artist_name":"Sean Kingston"}, {"id":3333333, "artist_name":"Mariah Carey"}]',
"$[*]"
COLUMNS(
id INT PATH "$.id",
artist TEXT PATH "$.artist_name"
)
) AS str_json
)
SELECT
am.id AS artist_master_id,
tmp1.id AS tmp1_id,
tmp1.artist AS artist
FROM
tmp_artist tmp1
INNER JOIN
artist_master am
ON
tmp1.artist = am.artist_name
;
+------------------+----------+---------------+
| artist_master_id | tmp1_id | artist |
+------------------+----------+---------------+
| 39019400600856 | 22222222 | Sean Kingston |
| 471393202558247 | 3333333 | Mariah Carey |
| 606456507475634 | 12345678 | Justin Bieber |
+------------------+----------+---------------+
おまけ、メモ
MySQL 8.0.4では、オプティマイザはJSON、古いドキュメントを削除したり、新しいドキュメントを完全にカラムに書き込んだりする代わりに、カラムの部分的なインプレース更新を実行できます。 この最適化は、JSON_SET()関数を使用し、JSON値の部分的な更新に記載されている条件を満たすUPDATE文に対して実行できます 。
JSON_SET()
既存の値を置き換え、存在しない値を追加します。
JSON_INSERT()
既存の値を置き換えずに値を挿入します。
JSON_REPLACE()
既存の値だけが置き換え られます。
mysql> SET @j = '{ "a": 1, "b": [2, 3]}'; mysql> SELECT JSON_SET(@j, '$.a', 10, '$.c', '[true, false]'); +-------------------------------------------------+ | JSON_SET(@j, '$.a', 10, '$.c', '[true, false]') | +-------------------------------------------------+ | {"a": 10, "b": [2, 3], "c": "[true, false]"} | +-------------------------------------------------+ mysql> SELECT JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]'); +----------------------------------------------------+ | JSON_INSERT(@j, '$.a', 10, '$.c', '[true, false]') | +----------------------------------------------------+ | {"a": 1, "b": [2, 3], "c": "[true, false]"} | +----------------------------------------------------+ mysql> SELECT JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]'); +-----------------------------------------------------+ | JSON_REPLACE(@j, '$.a', 10, '$.c', '[true, false]') | +-----------------------------------------------------+ | {"a": 10, "b": [2, 3]} | +-----------------------------------------------------+
例
id = 2 の image.uriを、"new_directory/2.jpg"に更新する あれば、UPDATE、なければINSERTになります
UPDATE artist_master SET Ext = JSON_SET(Ext, '$.image.uri', 'new_directory/' || Id ||'.jpg') WHERE id = 2 ;
複数のJSONの要素をJOIN UPDATE同時に更新する場合は、こんな感じでかけました JSONとは直接的に関係ないですが（笑）trandataのimage_urlにある、拡張子を、REPLACE関数を使ってpngからjpgに変更しております
UPDATE artist_master a INNER JOIN trandata b ON a.UUID = b.AlbumId SET a.Ext = JSON_SET(a.Ext, '$.image.uri', REPLACE(b.image_url,'\.png','\.jpg'), '$.image.mime_type','image/jpeg' ) ;
JSONのカラム化
一部、JSONの要素をMySQLカラム化することにより、索引を設定することが可能になり、パフォーマンスアップが期待できます
- 例えば以下のテーブルがあったとします。
CREATE TABLE `artist_json` ( `id` bigint(20) NOT NULL, `ext` json DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
このテーブルの中身は以下だったとします
SELECT * FROM artist_json LIMIT 2 ; +------------+-------------------------------------------------------------------------------------------------------------------------------+ | id | ext | +------------+-------------------------------------------------------------------------------------------------------------------------------+ | 3984920787 | {"artist_id": "XB29231EEE1B4819A6983D2F679B4E7B", "created_at": "2017-06-15 13:53:07.000000", "artist_name": "Gardy Girault"} | | 4321849201 | {"artist_id": "A88755A4829E447AAA4C8C2C3503648B", "created_at": "2017-10-17 06:42:55.000000", "artist_name": "Mirabilis"} | +------------+-------------------------------------------------------------------------------------------------------------------------------+
上記のJSONの中で、artist_nameだけMySQLのカラムにしたい場合、Generated Columnコマンドで仮装列を追加することができます。
ALTER TABLE artist_json ADD COLUMN artist_name VARCHAR(100) GENERATED ALWAYS AS (json_unquote(json_extract(`ext`,'$.artist_name'))) VIRTUAL ; CREATE INDEX ix01_artist_json ON artist_json(artist_name) ;
結果以下のような定義になります。もちろん索引も有効です
CREATE TABLE `artist_json` ( `id` bigint(20) NOT NULL, `ext` json DEFAULT NULL, `artist_name` varchar(100) GENERATED ALWAYS AS (json_unquote(json_extract(`ext`,_utf8mb3'$.artist_name'))) VIRTUAL, PRIMARY KEY (`id`), KEY `ix01_artist_json` (`artist_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
