MySQL道普請便り

第211回MySQLで文字列で入ってしまったJSONを扱いたい

筆者の担当回では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.0.33を使用しております。

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を活用してみる[その1]で紹介したJSON_EXTRACT関数を使って取得してみます。

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_VALIDは文字列がJSONとして正しいかどうかを判定する関数です。文字列がJSONとして正しければ1を、不正な場合は0を返して、NULLの場合はそのままNULLを返します。

関数の挙動を確認してみましょう。まずは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_string_dataに使用してみましょう。

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_string_dataで正しいものだけJSONとして評価してみましょう。

JSON_VALIDを使用して、有効な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文で処理するのはしんどいので、知っておいても損はないかと思います。

おすすめ記事

記事・ニュース一覧