はじめに

以前弊社のブログで下記のような、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インスタンスの情報をレコードを分けて入れてます。

まずはデータを入れるテーブルを作成

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 mysql> SELECT * FROM ec2_data WHERE id = 1\G *************************** 1. row *************************** id: 1 jdoc: {"Reservations": [{"Groups": [], "OwnerId": "*****", "Instances": [{"Tags": [{"Key": ~~省略~~,"ReservationId": "*****"}]} mysql> SELECT JSON_PRETTY(jdoc) FROM ec2_data WHERE id = 1\G *************************** 1. row *************************** JSON_PRETTY(jdoc): { "Reservations": [ "Groups": [], "OwnerId": "**************", "Instances": [ "Tags": [ "Key": ~~省略~~, "ReservationId": "*****" 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 | +----+---------------+---------------+------------+ 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"] | +---------------+-----------------------------------------+ 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"} | +---------------+-------------------------------------------------+ 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"]} | +---------------+--------------------------------------------------------------+