MySQLのSELECT文でJSONオブジェクトの値を抽出・検索する方法（json_extract活用術）

MySQLでは、SELECT文の中でJSONオブジェクトに格納された値をそのまま条件として利用できます。鍵となるのがjson_extract()関数です。この記事では、実際にテーブルを作成し、JSON型カラムの値を抽出して検索する手順を順番に解説します。

1. JSON型カラムを持つテーブルを作成する

まず、JSON型のカラムを含むテーブルを作成します。

mysql> create table DemoTable
    -> (
    -> Data json
    -> );
Query OK, 0 rows affected (0.63 sec)

DataカラムがJSON型として定義されている点がポイントです。これにより、文字列としてではなく構造化されたJSONデータを保存できます。

2. サンプルデータを挿入する

次に、INSERTコマンドを使ってJSON形式のレコードを登録します。

mysql> insert into DemoTable values('{"Name": "John", "CountryName": "US"}');
Query OK, 1 row affected (0.33 sec)

mysql> insert into DemoTable values('{"Name": "Chris", "CountryName": "UK"}');
Query OK, 1 row affected (0.15 sec)

ここでは「名前」と「国名」を持つJSONオブジェクトを2件挿入しました。

3. 登録したデータを確認する

SELECT文でテーブル内のすべてのレコードを表示してみましょう。

mysql> select *from DemoTable;

出力結果

+----------------------------------------+
| Data                                   |
+----------------------------------------+
| {"Name": "John", "CountryName": "US"}  |
| {"Name": "Chris", "CountryName": "UK"} |
+----------------------------------------+
2 rows in set (0.01 sec)

4. json_extract()でJSONの値を検索する

いよいよ本題です。WHERE句の中でjson_extract()を使うことで、JSONオブジェクト内の特定のキーの値を取り出し、検索条件にできます。

mysql> SELECT *from DemoTable WHERE json_extract(Data, '$.CountryName') like '%US%';

出力結果

+---------------------------------------+
| Data                                  |
+---------------------------------------+
| {"Name": "John", "CountryName": "US"} |
+---------------------------------------+
1 row in set (0.00 sec)

この例では、$.CountryNameというパス指定によって「CountryName」キーの値を取得し、LIKE '%US%'と部分一致させることで、「US」を含むレコードだけが抽出されました。

補足：json_extract()の書き方のポイント

• 第1引数には対象のJSONカラム名、第2引数には取得したいキーへのパスを指定します。
• パスは'$.キー名'の形式で記述します。ネストしたオブジェクトの場合は'$.親キー.子キー'のようにドットでつなぎます。
• 配列の要素を取得したい場合は'$[0]'のようなインデックス指定も可能です。
• MySQL 5.7以降では、Data->>'$.CountryName'というショートハンド演算子でも同じ結果が得られます。

このようにjson_extract()を活用すれば、JSONカラムの中身も通常のカラムと同じ感覚で柔軟に検索できるようになります。APIレスポンスの保存やログデータの管理など、JSONを扱う場面でぜひ役立ててください。