MySQLのJSONテーブル関数は調査に使うと便利
始めに
最近、不具合調査でAWSのCloudWatchから出力したログを分析および加工するためにDuckDBを使用しています。DuckDBは優秀で自分のやりたいように分析できるのですが、あくまでアプリケーションログをそのまま分析しているだけですので、最終的にはDBのMySQLと突合しないとわからないことが多々あります。
これがDBの値だけを取得するだけなら簡単だったのですが、IPアドレス制限で引っかかったユーザ一名、 該当IPアドレスおよびアクセス時間を出力したい要件だったため、アプリケーションログとDBの値を出力する必要がありました。もちろん、これが1行だけなら次のSQLで簡単に出力できます。
SELECT users.name, '192.0.2.1', '2025-04-12 12:00:00'
FROM users
WHERE id = '111111'
ただし、当然1行だけで済むわけはなく、一覧で出力する必要がありました。この時に便利だったのが、MySQLのJSONテーブル関数です。JSONテーブル関数を使用することで、容易に一覧で出力できました。
この魅力を伝えるためにブログにします。
環境
- MySQL
- 8.4
※ JSONテーブル関数は8.0.4以降で使用できます。
前提
アプリケーションログからは、ユーザーのID、 アクセスIP、アクセス時間が取得できるものとします。
実装
今回はDuckDBで抽出した例を記載します。アプリケーションログからユーザID、 アクセスIP、アクセス時間をJSON形式で抽出します。
SELECT user_id, ip_address, timestamp
FROM (
SELECT
DISTINCT "@message" ->> '$.message.object.message' AS message,
regexp_extract(message, 'user_id: ([a-f0-9-]+)', 1) AS user_id,
regexp_extract(message, 'ip: (\d+\.\d+\.\d+\.\d+)', 1) AS ip_address,
"@timestamp" as timestamp
FROM read_json_auto(
'/Users/kirimaru/Downloads/logs-insights-results.json', maximum_object_size = 1073741824
)
)
;
上で検索かけた後にDataGripのJSON形式の出力をすることで次のようなJSONが出力されます。
[
{"user_id":"11111","ip_address":"192.0.2.1", "timestamp": "2025-04-01T00:00:00"},
{"user_id":"22222","ip_address":"198.51.100.1", "timestamp": "2025-04-01T01:00:00"},
{"user_id":"33333","ip_address":"203.0.113.1", "timestamp": "2025-04-01T02:00:00"}
]
このJSONをもとに、MySQLのJSONテーブル関数を使用すると、DBの値とアプリケーションログを一気に出力できます。
SELECT
u.name,
j.ip_address,
j.timestamp
FROM users u
JOIN JSON_TABLE(
'[
{"user_id":"11111","ip_address":"192.0.2.1", "timestamp": "2025-04-01T00:00:00"},
{"user_id":"22222","ip_address":"198.51.100.1", "timestamp": "2025-04-01T01:00:00"},
{"user_id":"33333","ip_address":"203.0.113.1", "timestamp": "2025-04-01T02:00:00"}
]
',
'$[*]' COLUMNS(
user_id VARCHAR(36) PATH '$.user_id',
ip_address VARCHAR(15) PATH '$.ip_address',
timestamp DATETIME PATH '$.timestamp'
)
) j ON u.id = j.user_id
ORDER BY j.timestamp
;
他の用途に関しては、公式ヘルプを見ることがわかりやすいので公式ヘルプを見てください。
できなかったこと
ローカルファイルの読み込み
MySQLにはLOAD_FILE関数があるので、ローカルファイルを読み込めるかと思ったのですができませんでした。ファイルをDBサーバに配備する必要があり、手間が大変だったのでやるのを諦めました。
-- 容量及び配備可能なディレクトリを探す
SHOW VARIABLES LIKE 'max_allowed_packet';
SHOW VARIABLES LIKE 'secure_file_priv';
-- DBサーバのローカルを読み込もうとした
SELECT LOAD_FILE('/var/lib/mysql-files/test.json');
-- けど、このエラーを見かけたタイミングで調査を切り上げました
-- [22001][3144] Data truncation: Cannot create a JSON value from a string with CHARACTER SET 'binary'.
ソースコード
なし。
終わりに
今回のユースケースはスプレッドシートやExcelを使って、VLOOKUPやXLOOKUPを使って代用可能な機能ではあります。しかし、一つのIDE内で操作が完結するというメリットも大きく、事前にJSONファイルを用意するだけでDBと突合することが可能なので、もし同じような調査をするときに頭の片隅にでもJSONテーブル関数を使用するという選択肢が生まれると幸いです。
Discussion