問題点

MySQL5.7からはJSON型をサポートしており、JSON型のカラムから「〜の要素よこせ」ってSQL送るとJSONの中から、その要素のデータのみを取得できる。それ自体はけっこう便利なのだが、そのデータを加工しようとしたらコケたのでメモ

JSON_EXTRACTを使って取得。これは特に加工してないので文字列として返ってくる

試してみる

SELECT JSON_EXTRACT('{"created_at": "2018-03-12 21:54"}', '$.created_at');
>"2018-03-12 21:54"

次に取得した値をstr_to_dateを使ってDATETIME型に変換しようとする。

SELECT str_to_date(JSON_EXTRACT('{"created_at": "2018-03-12 21:54"}', '$.created_at'), '%Y-%m-%d %H:%i');
>NULL

するとNULLになってしまう。なんでやねん

原因は返ってくるデータがダブルクォーテーション含んでいるからであり、JSON_UNQUOTE()をはさんで一回除去する必要がある。

SELECT str_to_date(JSON_UNQUOTE(JSON_EXTRACT('{"created_at": "2018-03-12 21:54"}', '$.created_at')), '%Y-%m-%d %H:%i');

すげー見づらいんだが？？？？？？？MariaDB様のときはフツーにいけたのに