筆者の担当回では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文で処理するのはしんどいので、知っておいても損はないかと思います。