第197回　 MySQLでJSONを活用してみる［その1］

皆さんがWeb APIを設計する時によく使うフォーマットにJSONがあります。他サービスとやり取りする際に、従来であればJSONの解析を行い、正規化を行い、必要なキーとバリューを取得し保存するという方法が一般的でした。

今回は、MySQL 5.7からサポートが強化されているJSONを活用する方法を紹介していきたいと思います。

検証環境

今回は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_data` (id SERIAL, doc json);

カラムにはSERIAL型のidとjson型のdocカラムを用意しています。

JSONを保存してみる

それでは、まずはjson型のデータを保存してみましょう。といっても難しいことはありません。docカラムにJSON形式のデータを文字列で入れてINSERTをしていきます。

mysql> INSERT INTO json_data(doc) VALUES ('[1, [2, 3], {"4": "5" }]');
mysql> INSERT INTO json_data(doc) VALUES ('{"a": {"b": ["c", "d"]}, "e": "f"}');

データが挿入できました。とりあえずSELECTしてみましょう。

mysql> select * from json_data;
+----+------------------------------------+
| id | doc                                |
+----+------------------------------------+
|  1 | [1, [2, 3], {"4": "5"}]            |
|  2 | {"a": {"b": ["c", "d"]}, "e": "f"} |
+----+------------------------------------+
2 rows in set (0.02 sec)

さて、ここで破損したJSONを入れてみましょう。

mysql> INSERT INTO json_data(doc) VALUES ('{"a": ');

上記のように、キーの後にVALUEがなく閉じ括弧が無い不正なJSONを挿入しようとしてみます。結果は以下のようになり、不正なJSONとして弾かれていることがわかります。

ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 6 in value for column 'json_data.doc'.

txt型やblob型でもJSONは保存できたのですが、JSONとしての正しさを保証してくれるところに良さがあります。

JSONを検索してみる

さて、MySQLにJSONを挿入することができました。では続いてJSONの値を検索してみたいと思います。検索する方法としては基本的には2つあって、JSON_EXTRACTを使用する方法と、カラムパスを使用する方法があります。

JSON_EXTRACTを使用してみる

JSON_EXTRACTは、第一引数に渡したjson型のobjectを第ニ引数以降で指定したパスに存在するデータを返す関数になります。一致するものが存在しない場合、NULLが返ってきます。

mysql> SELECT * FROM json_data WHERE JSON_EXTRACT(doc, '$[0]') = 1;
+----+-------------------------+
| id | doc                     |
+----+-------------------------+
|  1 | [1, [2, 3], {"4": "5"}] |
+----+-------------------------+
1 row in set (0.04 sec)

上記のクエリではJSONの配列の0番目の値をとってきています。$[0]は配列へのアクセスとなっていて、JSON_EXTRACTをした場合、先頭の1が取得されます。

下記のようなクエリを実行して、どのような値が取得されているか確認してみましょう。

mysql> SELECT id, doc, JSON_EXTRACT(doc, '$[0]') FROM json_data WHERE JSON_EXTRACT(doc, '$[0]') = 1;
+----+-------------------------+---------------------------+
| id | doc                     | JSON_EXTRACT(doc, '$[0]') |
+----+-------------------------+---------------------------+
|  1 | [1, [2, 3], {"4": "5"}] | 1                         |
+----+-------------------------+---------------------------+
1 row in set (0.01 sec)

想定通り1が返っていることがわかります。

では続いて、配列ではなくオブジェクトの値を取得する場合を試してみましょう。その場合は下記のように'$.key'の形で指定をしてみましょう。

mysql> SELECT id, doc, JSON_EXTRACT(doc, '$.e') FROM json_data WHERE JSON_EXTRACT(doc, '$.e') = 'f';
+----+------------------------------------+--------------------------+
| id | doc                                | JSON_EXTRACT(doc, '$.e') |
+----+------------------------------------+--------------------------+
|  2 | {"a": {"b": ["c", "d"]}, "e": "f"} | "f"                      |
+----+------------------------------------+--------------------------+
1 row in set (0.02 sec)

上記では、eというキーを指定して"f"を取得しました。このように取得することができます。また、もっと複雑なpathの指定もできます。

mysql> SELECT id, doc, JSON_EXTRACT(doc, '$.a.b[0]') FROM json_data WHERE JSON_EXTRACT(doc, '$.a.b[0]') IS NOT NULL;
+----+------------------------------------+-------------------------------+
| id | doc                                | JSON_EXTRACT(doc, '$.a.b[0]') |
+----+------------------------------------+-------------------------------+
|  2 | {"a": {"b": ["c", "d"]}, "e": "f"} | "c"                           |
+----+------------------------------------+-------------------------------+
1 row in set (0.02 sec)

上記では、'$.a.b[0]'のpathにアクセスをしています。結果は"c"となっているので、解析してアクセスできていることがわかります。今回はスカラー値での比較を行っていますが、配列やオブジェクトを取得することもできます。

mysql> SELECT id, doc, JSON_EXTRACT(doc, '$.a.b') FROM json_data WHERE id = 2;
+----+------------------------------------+----------------------------+
| id | doc                                | JSON_EXTRACT(doc, '$.a.b') |
+----+------------------------------------+----------------------------+
|  2 | {"a": {"b": ["c", "d"]}, "e": "f"} | ["c", "d"]                 |
+----+------------------------------------+----------------------------+
1 row in set (0.01 sec)

mysql> SELECT id, doc, JSON_EXTRACT(doc, '$.a') FROM json_data WHERE id = 2;
+----+------------------------------------+--------------------------+
| id | doc                                | JSON_EXTRACT(doc, '$.a') |
+----+------------------------------------+--------------------------+
|  2 | {"a": {"b": ["c", "d"]}, "e": "f"} | {"b": ["c", "d"]}        |
+----+------------------------------------+--------------------------+
1 row in set (0.01 sec)

JSON_EXTRACTの省略記法（->）を使用する

JSON_EXTRACTで最低限JSONオブジェクトにアクセスして値を取得することができるようになりましたが、JSON_EXTRACTを複数書く場合を考えてみましょう。たとえばWHERE句に複数の値を指定して検索する場合に、JSON_EXTRACTが大量に並ぶとクエリが見づらくなってしまうと思います。そんな場合はJSON_EXTRACTの省略記法の->を使用してみましょう。

ここでは、$.a.b[0]が"c"で$.eが"f"の値を検索する場合を考えてみましょう。

mysql> SELECT id, doc FROM json_data WHERE JSON_EXTRACT(doc, '$.a.b[0]') = 'c' AND JSON_EXTRACT(doc, '$.e') = 'f';
+----+------------------------------------+
| id | doc                                |
+----+------------------------------------+
|  2 | {"a": {"b": ["c", "d"]}, "e": "f"} |
+----+------------------------------------+
1 row in set (0.01 sec)

ベタに書くと上記のようになります。これを->を使って書き換えると以下のようになります。

mysql> SELECT id, doc FROM json_data WHERE doc->'$.a.b[0]' = 'c' AND doc->'$.e' = 'f';
+----+------------------------------------+
| id | doc                                |
+----+------------------------------------+
|  2 | {"a": {"b": ["c", "d"]}, "e": "f"} |
+----+------------------------------------+
1 row in set (0.01 sec)

クエリ自体の文字数がだいぶ短くなって、見やすくなったと思います。

JSON_PRETTY関数で綺麗に出力する

今回出力しているJSONは、キーも入っている値も1文字なので見やすいと思うのですが、実環境のJSONはもっと長いキーが指定されていたりして、1行にまとめられてしまうと読みにくい場合があります。そんな場合に使えるのがJSON_PRETTYになります。

mysql> SELECT  JSON_PRETTY(doc) FROM json_data WHERE id = 1;
+-----------------------------------------------------+
| JSON_PRETTY(doc)                                    |
+-----------------------------------------------------+
| [
  1,
  [
    2,
    3
  ],
  {
    "4": "5"
  }
] |
+-----------------------------------------------------+
1 row in set (0.01 sec)

今回のようにキーが小さい場合は逆に見えづらくなってしまう場合もありますが、実環境で検索をする際には、知っておいて損はないと思います。他のカラムを含めて出す場合などは、反対に見えづらくなってしまうので注意しましょう。

mysql> SELECT  id,JSON_PRETTY(doc),id FROM json_data WHERE id = 1;
+----+-----------------------------------------------------+----+
| id | JSON_PRETTY(doc)                                    | id |
+----+-----------------------------------------------------+----+
|  1 | [
  1,
  [
    2,
    3
  ],
  {
    "4": "5"
  }
] |  1 |
+----+-----------------------------------------------------+----+
1 row in set (0.01 sec)

まとめ

今回は、MySQLでJSON型のデータを挿入する方法と基本的な検索方法を紹介しました。今回紹介した関数以外にも便利な関数が追加されていて、より使いやすくなっているので、今後も紹介していきたいと思ってます。

他社連携のAPIで取得した結果をログとして保存して検索をしたい場合に（MySQLにそういったログを入れることの是非はありますが⁠）⁠、証跡として残すと同時に、Generated ColumnやJSON操作の関数などと組み合わせてそのまま活用することもできます。無闇矢鱈に使うことを推奨はしませんが、ワークフローに合致する場合もあると思いますので、適宜使っていきましょう。