MySQL8.0 : 新しく追加されたJSON関数サンプル

無事、システムもMySQL 8.0化が成功し、じゃあ今後、どう活用していこうと考えた時に、まずやったのが、WITH句なんですが、

hit.hateblo.jp

他にもいっぱい機能がアップされた部分があって、その中で、今回JSONで追加された関数を主に試してみました。

今回は、以下の関数を試してみたいと思います

JSON_TABLE
JSON_ARRAYAGG
JSON_OBJECTAGG
JSON_PRETTY
JSON_OBJECT
JSON_EXTRACT
JSON_UNQUOTE

こんなサンプルを使って説明します

テストテーブルを作成

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 |
+------------------+----------+---------------+

f:id:hit10231023:20180309104332j:plain

おまけ、メモ

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

Database JUNKY

MySQL,MariaDBを中心としたブログです