10
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

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

Last updated at Posted at 2019-08-07

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> 

取得できました!

10
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
10
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?