はじめに
以前弊社のブログで下記のような、MySQLでJSONを扱う記事をアップさせていただきました。
MySQLでJSON型を使う（基本編）
MySQLでJSON型を使う（パフォーマンス編）
MySQLでJSON型を使う（論理設計編）
今回はJSON関数に関してのアップデートについてご紹介したいと思います。
記載する内容は下記のファンクションです。
- JSON_PRETTY()
- JSON_ARRAYAGG()
- JSON_OBJECTAGG()
なおJSON周りのファンクションについての公式ページはこちらです。
今回のMySQL Serverのバージョンは [ 8.0.15 MySQL Community Server – GPL ]で確認しています。
JSON_PRETTY()
最近よくawscliを使用して、データを取得するのですが、その際に取得出来る
データをMySQLに入れてみました。
以下のように、各EC2インスタンスの情報をレコードを分けて入れてます。
まずはデータを入れるテーブルを作成
|
1 2 3 4 5 6 7 8 9 10 |
mysql> CREATE TABLE ec2_data(id INT NOT NULL AUTO_INCREMENT, jdoc JSON NOT NULL, PRIMARY KEY(id)); # 使用しているEC2インスタンスのIDを取得 $ aws ec2 describe-instances | jq -r '.Reservations[].Instances[].InstanceId' > ec2_ids.log # 取得したIDを利用して各インスタンスのデータをファイルに出力 $ cat ec2_ids.log | while read line ;do aws ec2 describe-instances --instance-ids=${line} > ${line}.json ;done # ファイル内のデータをINSERT $ for f in *.json ; do mysql --login-path=esxi2 aws_data -e "LOAD DATA LOCAL INFILE '$f' INTO TABLE ec2_data FIELDS ESCAPED BY '\n' (jdoc) "; done |
以上でデータのロードは終了です。
確認してみます。
|
1 2 3 4 |
mysql> SELECT * FROM ec2_data WHERE id = 1\G *************************** 1. row *************************** id: 1 jdoc: {"Reservations": [{"Groups": [], "OwnerId": "*****", "Instances": [{"Tags": [{"Key": ~~省略~~,"ReservationId": "*****"}]} |
データをテーブルにロードする際に、改行とインデントを除いているため、非常に見辛いです。
そんな時はJSON_PRETTY()が便利です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> SELECT JSON_PRETTY(jdoc) FROM ec2_data WHERE id = 1\G *************************** 1. row *************************** JSON_PRETTY(jdoc): { "Reservations": [ { "Groups": [], "OwnerId": "**************", "Instances": [ { "Tags": [ { "Key": ~~省略~~, "ReservationId": "*****" } ] } |
改行とインデントが入り、awscliで見慣れた形式で出力してくれます。
SELECTの結果にも使えるので、例えば下記の実行すると特定のSecurityGroupIdを使用しているインスタンスだけを抜き出して、その結果を見易くしたりも出来ます。
|
1 |
mysql> SELECT JSON_PRETTY(jdoc) FROM ec2_data WHERE jdoc -> '$[0]."Reservations"[0]."Instances"[0]."SecurityGroups"[0]."GroupId"' = "セキュリティグループID" \G |
JSON_ARRAYAGG()
こちらはテーブルデータをJSON配列にしてくれる集計関数です。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> SELECT * FROM instances; +----+---------------+---------------+------------+ | id | instance_name | tag_name | tag_values | +----+---------------+---------------+------------+ | 1 | testA | Name | smart | | 2 | testA | Name | style | | 3 | testB | Name | Dummy | | 4 | testB | Name | Company | | 5 | testA | StorageEngine | InnoDB | | 6 | testB | StorageEngine | MyISAM | +----+---------------+---------------+------------+ |
上記のようなテーブルがあった時に
|
1 2 3 4 5 6 7 |
mysql> SELECT tag_name, JSON_ARRAYAGG(tag_values) FROM instances GROUP BY tag_name; +---------------+-----------------------------------------+ | tag_name | JSON_ARRAYAGG(tag_values) | +---------------+-----------------------------------------+ | Name | ["smart", "style", "Dummy", "Commpany"] | | StorageEngine | ["InnoDB", "MyISAM"] | +---------------+-----------------------------------------+ |
特定のキーに対して、値を配列で出すことが出来ます。
JSON_OBJECTAGG()
そしてこちらは、テーブルデータをJSONオブジェクトにしてくれる集計関数です。
気にしなければいけないポイントとして、キーが重複している場合、下記のように上書きされてしまいます。
|
1 2 3 4 5 6 7 |
mysql> SELECT instance_name, JSON_OBJECTAGG(tag_name, tag_values) as tags FROM instances GROUP BY instance_name; +---------------+-------------------------------------------------+ | instance_name | tags | +---------------+-------------------------------------------------+ | testA | {"Name": "style", "StorageEngine": "InnoDB"} | | testB | {"Name": "Company", "StorageEngine": "MyISAM"} | +---------------+-------------------------------------------------+ |
そのため、WITH句を使用するなどして対応が必要になります。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> WITH cte AS -> ( -> SELECT instance_name, tag_name, JSON_ARRAYAGG(tag_values) tag_values FROM instances GROUP BY instance_name, tag_name -> ) -> SELECT instance_name, JSON_OBJECTAGG(tag_name, tag_values) FROM cte GROUP BY instance_name; +---------------+--------------------------------------------------------------+ | instance_name | JSON_OBJECTAGG(tag_name, tag_values) | +---------------+--------------------------------------------------------------+ | testA | {"Name": ["smart", "style"], "StorageEngine": ["InnoDB"]} | | testB | {"Name": ["Dummy", "Company"], "StorageEngine": ["MyISAM"]} | +---------------+--------------------------------------------------------------+ |
まとめ
上記のようなデータはawscliやjqコマンドなどでも抜き出すことは可能だと思います。
ただ、普段からMySQLを扱っている方には、JSONを扱えることが増えると色々捗りますよね。
