筆者の担当回ではMySQLのJSONの活用方法に関して説明をしてきましたが、以前のバージョンからの引き継ぎでJSONを文字列として保存してしまっている場合もあると思います。また、他のAPIから返された値を保存しておきたいこともありますが、あまり信頼できない場合には、いったん文字列で保存したい場合もあると思います。
今回は、そんな時にMySQLの便利な関数を使う方法を紹介していきます。
検証環境
今回はDockerで建てたMySQLを使用します。以下のコマンドでDockerを建ててローカルからアクセスをします。
% docker run --platform linux/x86_64 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest
アクセス方法は以下の通りになります。
% mysql -uroot -pmy-secret-pw -h127.0.0.1 -P3307
執筆時点では、以下の通りMySQL 8.
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.33 | +-----------+ 1 row in set (0.01 sec)
今回はtestデータベースとdataテーブルを作成します。
mysql> create database test; mysql> use test mysql> CREATE TABLE `json_string_data` (id SERIAL, doc TEXT);
カラムにはSERIAL型のidとjson型のdocカラムを用意しています。
文字列としてJSONを入れてみる
JSONを文字列としてMySQLにINSERTしてみましょう。
mysql> INSERT INTO json_string_data(doc) VALUES ('{}'), ('{"a": 1}');
INSERTしたのは空のオブジェクト、そしてaというキーを持つオブジェクトとなります。
mysql> select * FROM json_string_data;
+----+----------+
| id | doc |
+----+----------+
| 1 | {} |
| 2 | {"a": 1} |
+----+----------+
2 rows in set (0.00 sec)
文字列をJSONに変換してみる
文字列をJSONに変換するにはCAST関数を使用します。とりあえず、いったん空っぽのオブジェクトをJSONに変換してみましょう。関数を試すには、SELECT文単体で確認します。
mysql> SELECT CAST('{}' as JSON);
+--------------------+
| CAST('{}' as JSON) |
+--------------------+
| {} |
+--------------------+
1 row in set (0.00 sec)
ということで、簡単に変換することができました。
次に、オブジェクトにaというキーがあるJSONの文字列をJSON型に変換してみましょう。第197回 MySQLでJSONを活用してみる
mysql> SELECT JSON_EXTRACT(CAST('{"a": 1}' as JSON), '$.a');
+-----------------------------------------------+
| JSON_EXTRACT(CAST('{"a": 1}' as JSON), '$.a') |
+-----------------------------------------------+
| 1 |
+-----------------------------------------------+
1 row in set (0.00 sec)
ここまできたらもう簡単ですね。docをキャストしてあげればJSONとして使えそうだとわかります。
単純にJSONに変換して検索をしてみる
では検索をしてみましょう。aというキーに1が指定されているオブジェクトを探してみます。
mysql> SELECT * FROM json_string_data WHERE JSON_EXTRACT(CAST(doc as JSON), '$.a') = 1;
+----+----------+
| id | doc |
+----+----------+
| 2 | {"a": 1} |
+----+----------+
1 row in set (0.00 sec)
これで単純なJSONなら問題なく検索できることがわかりました。ただし、このJSON型を文字列で入れてしまった理由を思い出してみましょう。そうです。何かおかしな値が入ってしまっている可能性があります。次にそんな場合にどうしたら良いか考えてみましょう。
JSONとして壊れた文字列がある場合
壊れている例として、CASTに失敗する文字列を2パターンほど用意します。空文字の場合や閉じカッコを忘れてしまった場合を考えてみます。
まずは空文字を試してみます。SELECT文で試してみましょう。
mysql> SELECT CAST('' as JSON);
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json: "The document is empty." at position 0.
ドキュメントが空のためJSONとして不正と言われてしまいました。
続いて壊れたJSONを試してみましょう。
mysql> SELECT CAST('{' as JSON);
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json: "Missing a name for object member." at position 1.
こちらもJSONとして不正と言われました。
これらを先ほどのテーブルに挿入してみましょう。
mysql> INSERT INTO json_string_data(doc) VALUES (''), ('{'), (NULL);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
この状態で先ほどのクエリを実行してみましょう。
mysql> SELECT * FROM json_string_data WHERE JSON_EXTRACT(CAST(doc as JSON), '$.a') = 1; ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json: "The document is empty." at position 0.
エラーになってしまったことがわかります。ならば壊れているJSONを除外してあげれば、と思うかもしれませんが、一体どれがJSONとして正しくどれが不正か、数が増えた場合に把握するのは困難です。
このようにJSONに不正な文字列が入っている場合、検索することができなくなってしまうことがわかります。
ちなみに、NULLはNULLとして処理されるため問題ありません。
mysql> SELECT CAST(NULL as JSON); +--------------------+ | CAST(NULL as JSON) | +--------------------+ | NULL | +--------------------+ 1 row in set (0.00 sec)
JSON_VALIDを使ってみる
JSON_
関数の挙動を確認してみましょう。まずはJSONとして有効な場合を試してみます。
mysql> SELECT JSON_VALID('{}');
+------------------+
| JSON_VALID('{}') |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
JSONとして正しいので1が返っています。続いて不正な場合を確認してみます。
mysql> SELECT JSON_VALID('{');
+-----------------+
| JSON_VALID('{') |
+-----------------+
| 0 |
+-----------------+
1 row in set (0.00 sec)
0が返っていることがわかります。続いてNULLを試してみましょう。
mysql> SELECT JSON_VALID(NULL); +------------------+ | JSON_VALID(NULL) | +------------------+ | NULL | +------------------+ 1 row in set (0.00 sec)
これをjson_
mysql> select *, JSON_VALID(doc) FROM json_string_data;
+----+----------+-----------------+
| id | doc | JSON_VALID(doc) |
+----+----------+-----------------+
| 1 | {} | 1 |
| 2 | {"a": 1} | 1 |
| 3 | | 0 |
| 4 | { | 0 |
| 5 | NULL | NULL |
+----+----------+-----------------+
5 rows in set (0.00 sec)
これで、不正なものと正しいものが判別ができるようになりました。では、json_
JSON_
mysql> select id FROM json_string_data WHERE JSON_VALID(doc) = 1 OR JSON_VALID(doc) IS NULL; +----+ | id | +----+ | 1 | | 2 | | 5 | +----+ 3 rows in set (0.00 sec)
有効なJSONをもつdocのidが返っていることがわかります。先ほど作ったクエリをサブクエリとして活用して、正しいJSONのidだけ抜き出してIN句の条件にしてみましょう。
mysql> SELECT id, CAST(doc as JSON) FROM json_string_data WHERE id IN (select id FROM json_string_data WHERE JSON_VALID(doc) = 1 OR JSON_VALID(doc) IS NULL);
+----+-------------------+
| id | CAST(doc as JSON) |
+----+-------------------+
| 1 | {} |
| 2 | {"a": 1} |
| 5 | NULL |
+----+-------------------+
3 rows in set (0.01 sec)
欲しい結果が得られたことがわかります。これでJSONとして不正な文字列が入っている場合でも、MySQLのJSON型の関数を活用することができるようになりました。
まとめ
今回は、MySQLにテキストとして保存してしまったJSONファイルを、なんとか工夫してJSONの関数を使用する方法を紹介しました。
しかし、本来はJSONとしてinvalidなものであれば別途処理する等の方がアプリケーションとしての挙動としては正しく、バリデーションをサボった結果と言えます。当然JSONに対してindex等が使えないため、処理自体は重いものになってしまいます。
とはいえ、そんなことはわかっている… わかっているけど、…出くわしてしまった場合になんとかしないといけない、といったことがあると思います。そんな時に文字列としてLIKE文で処理するのはしんどいので、知っておいても損はないかと思います。