kakakakakku blog

Weekly Tech Blog: Keep on Learning!

MySQL で JSON 型からクオートを除去した文字列を取得するなら ->> を使う

MySQL(5.7 以降)で「JSON 型」のカラムから指定したキーを取得する場合 JSON_EXTRACT() を使う.もしくは JSON_EXTRACT() と同じ動作をする -> オペレータを使うこともできる.以下に members テーブルの info カラムから name キーを取得する SQL を載せる.なお,今回は MySQL 8.0.19 を検証環境にした.

-- サンプルテーブルを作成する
mysql> CREATE TABLE members ( info json DEFAULT NULL );
Query OK, 0 rows affected (0.10 sec)

-- サンプルデータを追加する
mysql> INSERT INTO members VALUES ('{"id": 1, "name": "kakakakakku"}');
Query OK, 1 row affected (0.10 sec)

-- サンプルデータを確認する
mysql> SELECT * FROM members;
+----------------------------------+
| info                             |
+----------------------------------+
| {"id": 1, "name": "kakakakakku"} |
+----------------------------------+
1 row in set (0.01 sec)

-- JSON_EXTRACT() を使って取得する
mysql> SELECT JSON_EXTRACT(info, '$.name') FROM members;
+------------------------------+
| JSON_EXTRACT(info, '$.name') |
+------------------------------+
| "kakakakakku"                |
+------------------------------+
1 row in set (0.01 sec)

-- -> を使って取得する
mysql> SELECT info->'$.name' FROM members;
+----------------+
| info->'$.name' |
+----------------+
| "kakakakakku"  |
+----------------+
1 row in set (0.01 sec)

JSON_UNQUOTE() と組み合わせる

実際に JSON_EXTRACT() を使って文字列を取得すると "kakakakakku" のように「ダブルクオート付き」になる.例えば CHARVARCHAR と比較するときに困るため,JSON_UNQUOTE() と組み合わせるとクオートを除去できる.

-- JSON_UNQUOTE(JSON_EXTRACT()) を使って取得する
mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT(info, '$.name')) FROM members;
+--------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT(info, '$.name')) |
+--------------------------------------------+
| kakakakakku                                |
+--------------------------------------------+
1 row in set (0.01 sec)

->> オペレータを使う

JSON_UNQUOTE(JSON_EXTRACT()) を使うと JSON_UNQUOTE(JSON_EXTRACT(info, '$.name')) のように長くなってしまう.箇所が多いと SQL の可読性に影響する可能性もある.そこで JSON_UNQUOTE(JSON_EXTRACT()) と同じ動作をする ->> オペレータを使うこともできる.MySQL 5.7.13 で導入された ->> オペレータの存在は今まで気付いていなかった!

-- ->> を使って取得する
mysql> SELECT info->>'$.name' FROM members;
+-----------------+
| info->>'$.name' |
+-----------------+
| kakakakakku     |
+-----------------+
1 row in set (0.01 sec)

ドキュメントを読むと ->>equivalent to JSON_UNQUOTE(JSON_EXTRACT()) と書いてある.

名前 説明
-> Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
->> Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).

dev.mysql.com

まとめ

  • MySQL で「JSON 型」のカラムから指定したキーを取得する場合は JSON_EXTRACT()JSON_UNQUOTE() を組み合わせる
  • JSON_UNQUOTE(JSON_EXTRACT()) と同じ動作をする ->> オペレータを使うと短く書ける

関連記事

4年前に JSON_EXTRACT() など JSON を操作する関数を調査した記事も参考になった.とは言え,MySQL 5.7 も MySQL 8.0 も JSON を操作する関数が増えているため,もう1度調査をしておくと良さそう.

kakakakakku.hatenablog.com