フォームのエラーをMySQLのJSON型のカラムに入れて計測してみたらLATERAL導出テーブルで躓いた

はじめに

転職ドラフトでWebアプリケーションエンジニアをやっている iwtn です。

今回は、Webアプリケーションの機能を改善していく中で、実際に発生しているフォームでのバリデーションエラーを、MySQL 8.0で使えるようになったJSON型でログにとってみました。そして、それを集計する際に必要だった LATERAL導出テーブル というサブクエリーの機能について一緒に記事にしたいと思います。

バリデーションエラーの計測をする意義

Webアプリケーションにおいて、ユーザーが値を入力する際に実際にどういったエラーがでているのかは、UIを改善するときにとても大事な情報です。開発者やPdMが「なんとなく使いにくいなぁ」と感じたことのような定性的な情報を元にした改善もできますが、長い間触れているうちに慣れてしまい、実際のユーザーと感覚が乖離してしまうことはよくあります。

そこで実際に出ているエラーをサクッと記録しておくのに、MySQL 8.0から導入されたJSONデータ型を使ってみました。

特にRailsのバリデーションエラーは簡単にJSON形式に変換できるので、そのままDBに放り込んでおけば記録はとれます。

ただし、JSONをSQLで集計していくのが非常に難しかったので、そのノウハウを共有できればと思います。

JSON_TABLEという難問

RDBは基本的にすべてがテーブル（リレーション）の世界です。
なので、JSONのデータをテーブルの形式にすることで、普段使っている集計用のクエリを使えるようになります。

MySQLでは、そのテーブルにする役割を担うのはJSON_TABLE関数です。

dev.mysql.com

ただ、上記のリンクにあるサンプルクエリなどは、テーブルの中にあるJSONデータを加工する形ではなく、JSON形式のデータを直接JSON_TABLE関数に渡すような書き方になっており、あまり参考になりませんでした。

集計のクエリの書き方を解説

以下のような定義の error_logs テーブルに格納するものとします。この他にどのフォームのエラーなのかを示すカラムを足して良いと思いますが、ここでは割愛します。

カラム名	データ型	説明	備考
id	bigint	主キー	レコードを一意に識別するID
errors_json	json	エラー情報	構造化されたデータを格納する
created_at	datetime	作成日時	レコードが作成された日時

errors_json の中身は以下のような、いわゆる「値が配列のオブジェクト」としてJSONに入っているものとします。

{
  "attribute1" => ["error1", "error2"],
  "attribute2" => ["error3", "error4", "error5"],
  "attribute3" => ["error6"]
}

JSON_TABLEを使ったクエリの書き方

私が最初に躓いたのが、JSON_TABLEをどこに書いて、渡すJSONのデータをどこから持ってくるのか、でした。

まず「どこに」ですが、JSON_TABLEの結果が一つのテーブルになるので、from句に書くことになります。
次に「データをどこから」ですが、これは error_logs テーブルから持ってくるしかありません。
よって、以下のような書き方になります。

SELECT
  el.id,
  jt.attribute AS attribute
FROM error_logs el,
     JSON_TABLE(JSON_KEYS(el.errors_json), '$[*]' COLUMNS (attribute VARCHAR(255) PATH '$')) AS jt

JSON_TABLE関数に渡しているのは、以下のようなものです。

第一引数はJSONデータ
第二引数は何を第一引数のJSONから抜き出すかを指定するJSONのPathで、ここでは第一引数のすべての配列の要素を示している
第三引数は結果のテーブルの型を定義するCOLUMNS句

第一引数で使っているJSON_KEYS関数は、JSONが渡された際にそのキーの一覧をJSON配列で返します。

dev.mysql.com

COLUMNS句にはJSONのどの値を取得するかのPathを渡せます。
また複数のカラムの定義を渡せるので、複数の列をもったテーブルをJSONのデータから作ることもできます。

このクエリの実行結果は、以下のような各エラーログのJSONのkeyになるattributeの一覧になるテーブルになります。

id	attribute
1	attribute1
1	attribute2
2	attribute3
⋮	⋮

LATERALの機能について

ここで不可解なのはFROM句の中身です。 , だけでJOINしているように見えるので、CROSS JOIN のように見えますが、これは LATERAL導出テーブル と呼ばれる機能が使われます。

この LATERAL導出テーブル がどう機能するかというと、前のテーブルから1行ずつ取り出してサブクエリー（ここではJSON_TABLE関数）を実行し、その結果を前のテーブルから取り出した行と結合していくという動きになります。

dev.mysql.com

なので、CROSS JOINのようにすべての行とは組み合わせられず、結果は爆発的な量のレコードをもったテーブルにならずに済みます。

更にエラーと結合して集計する

次に、エラーの内容を展開していきます。

先程のattributeの一覧のテーブルをwith句に入れつつ、元の error_logs テーブルとLEFT JOINし、さらにまたJSON_TABLEの結果をサブクエリーとして使っています。

WITH error_attributes AS (
SELECT
  el.id,
  jt.attribute AS attribute
FROM error_logs el,
     JSON_TABLE(JSON_KEYS(errors_json), '$[*]' COLUMNS (attribute VARCHAR(255) PATH '$')) AS jt)

SELECT
  el.id,
  ea.attribute,
  errors.error
FROM error_logs el
LEFT JOIN error_attributes ea on ea.id = el.id,
JSON_TABLE(JSON_EXTRACT(el.errors_json, CONCAT('$."', ea.attribute,'"')), '$[*]' COLUMNS (error VARCHAR(255) PATH '$')) AS errors

JSON_EXTRACT(el.errors_json, CONCAT('$."', ea.attribute,'"')) の部分が特に難しかったです。 JSON_EXTRACT関数は、第一引数のJSONのデータから、第二引数のJSON Pathに該当する部分を返します。

dev.mysql.com

ここでCONCAT関数を使って attribute に該当するエラー部分の配列が返るようなJSON Pathを作っています。

そしてJSON_TABLE関数は、JSON_EXTRACT関数の結果のJSON配列を第一引数で受け取り、第二引数ですべての配列の要素を対象とし、第三引数で定義したのカラムを持ったテーブルとして返します。

結果は以下のような一覧のテーブルになります。

id	attribute	error
1	attribute1	error1
1	attribute1	error2
1	attribute1	error3
1	attribute2	error4
1	attribute2	error5
1	attribute3	error6
2	attribute4	error7
⋮	⋮	⋮

ここまでくれば、あとは attirbute や error で集計するだけです。フォームのどの項目でエラーが起きているのか、どんな種類のエラーが多いのかを集計できます。他に created_at などを使って、いつエラーが多いのかや、ログのテーブルにカラムを追加することでそちらで切り分けることもできます。