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>
取得できました!