mysqlのtext型に格納されたjson形式の値を取得する方法

mysqlのtext型にjson形式で格納された値をSQLで取り出す方法を調べたのでメモ。

MySQL5.7からJSON型が使えるようになったようです。
https://dev.mysql.com/doc/refman/8.0/en/json.html

取得元のテーブル

CREATE TABLE `order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_number` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

格納されている値

orderテーブルのoption_numberカラムにはこんな値が入っています。

{"adult": 2, "child": 1}

まずはJSON型にキャストしてみる

mysql> select CAST(option_number AS JSON) from tickets as ticket;
+---------------------------------------------------------------------+
| CAST(option_number AS JSON)                                                    |
+---------------------------------------------------------------------+
| {"adult": 1, "child": 0} |
| {"adult": 1, "child": 0} |
| {"adult": 0, "child": 1} |
| {"adult": 0, "child": 1} |

JSON型にした項目のキーを指定して値を取得

mysql> select CAST(option_number AS JSON)->"$.adult" from tickets as ticket;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '->"$.age-category-adult" from tickets as ticket' at line 1
mysql>

なぜかうまくいかない・・・
ネストしてみる。。。

mysql> select tickets.option_number->"$.adult"
    -> from(select CAST(option_number as JSON) as option_number from tickets) as tickets;
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 20.
mysql> 

こちらもだめ。日本語を含む場合キー値をダブルコーテーションで囲うと解決するという記事を発見。試してみる。

mysql> select tickets.option_number->'$."adult"'
    -> from(select CAST(option_number as JSON) as option_number from tickets) as tickets limit 1;
+-------------------------------------------------+
| tickets.option_number->'$."adult"'              |
+-------------------------------------------------+
| 1                                               |
| 1                                               |
| 0                                               |
| 0                                               |
+-------------------------------------------------+
1 row in set (0.01 sec)

mysql> 

取得できました！