JSON_EXTRACT chain with CAST or STR_TO_DATE is not running

Question

I am trying to extract datetime from JSONFIELD data in MySQL.

If I make a simple JSON_EXTRACT, the type of the returned field is JSON.

mysql> select JSON_EXTRACT(data, "$.new_time") from analytics limit 10; +----------------------------------+ | JSON_EXTRACT(data, "$.new_time") | +----------------------------------+ | NULL | | "2016-09-30T04:00:00+00:00" | | "2016-09-29T05:30:00+00:00" | | NULL | | "2016-10-01T05:30:00+00:00" | | "2016-09-27T23:00:00+00:00" | | NULL | | "2016-09-23T01:30:00+00:00" | | "2016-09-23T04:00:00+00:00" | | "2016-09-27T01:30:00+00:00" | +----------------------------------+ 

I want to convert this to MySQL DATETIME. But if I bind JSON_EXTRACT and STR_TO_DATETIME, I get all NULL values:

 mysql> select STR_TO_DATE(JSON_EXTRACT(data, "$.new_time") ,"%Y-%m-%d") from analytics_calendaranalytics limit 10; +-----------------------------------------------------------+ | STR_TO_DATE(JSON_EXTRACT(data, "$.new_time") ,"%Y-%m-%d") | +-----------------------------------------------------------+ | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | +-----------------------------------------------------------+ 

Similarly, CAST as DATETIME also fails:

 mysql> select CAST(JSON_EXTRACT(data, "$.new_time") as DATETIME) from analytics_calendaranalytics limit 10; +----------------------------------------------------+ | CAST(JSON_EXTRACT(data, "$.new_time") as DATETIME) | +----------------------------------------------------+ | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | | NULL | +----------------------------------------------------+ 

Both of these commands work when I start with a string value:

 mysql> select CAST("2016-09-30T04:00:00+00:00" as DATETIME); +-----------------------------------------------+ | CAST("2016-09-30T04:00:00+00:00" as DATETIME) | +-----------------------------------------------+ | 2016-09-30 04:00:00 | +-----------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select STR_TO_DATE("2016-09-30T04:00:00+00:00", "%Y-%m-%d"); +------------------------------------------------------+ | STR_TO_DATE("2016-09-30T04:00:00+00:00", "%Y-%m-%d") | +------------------------------------------------------+ | 2016-09-30 | +------------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) 

Thank any help in resolving this!

Answer 1

You must use JSON_UNQUOTE

 select CAST( JSON_UNQUOTE( JSON_EXTRACT(data, "$.new_time")) as DATETIME) from analytics_calendaranalytics limit 10; 

Will work. I say because you did not provide sample data. I tried the following:

 select @js := JSON_OBJECT('new_time',"2016-09-30T04:00:00+00:00" ); select CAST(JSON_UNQUOTE(JSON_EXTRACT(@js,'$.new_time')) as DATETIME); 

The following query also works

  select STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(@js,'$.new_time')) ,"%Y-%m-%d");