mysql - 将从json值中提取的字符串转换为返回NULL的日期

Question

我正在以 json 格式在一个数据库字段中保存一个日期。 json 值如下所示:

[{"key":"jkajdajsdiajsdik"},{"created_date":"2018-01-17 15:51:23"}]

我想通过 sql 从 json 中提取 created_date，我通过以下查询获取它。

select SUBSTRING_INDEX(SUBSTRING_INDEX(testjson, 'created_date\":', -1),'}',1) as created_date from test 

上面的查询返回

"2018-01-17 15:51:23"

现在我正在尝试将这个返回的字符串转换为日期。

select STR_TO_DATE(SUBSTRING_INDEX(SUBSTRING_INDEX(testjson, 'created_date\":', -1),'}',1),'%Y-%m-%d %H:%i:%s') as created_date from test 

但是这是返回 NULL。当我尝试使用示例时

SELECT STR_TO_DATE(" 2018-01-17 15:51:23",'%Y-%m-%d %H:%i:%s')

它回来了

2018-01-17 15:51:23

我做错了什么？谁能帮我解决这个问题？

提前致谢

Answer 1

Substring_Index() 操作后返回的日期也包含双引号。我以为 Str_to_Date() 函数指定的格式添加了双引号:

select STR_TO_DATE(
           SUBSTRING_INDEX(
               SUBSTRING_INDEX(testjson, 'created_date\":', -1),'}'
               ,1)
           ,'"%Y-%m-%d %H:%i:%s"') 
AS created_date from test 

结果

| created_date        |
| ------------------- |
| 2018-01-17 15:51:23 |

View on DB Fiddle

---

但是，在 5.7 及更高版本中，最好改用 JSON 函数。我们可以使用 Json_Extract()函数获取 created_date 键对应的值，并使用 Str_To_Date() 函数。

查询#2

SET @j := '[{"key":"jkajdajsdiajsdik"},{"created_date":"2018-01-17 15:51:23"}]';

SELECT STR_TO_DATE(JSON_EXTRACT(@j, '$[*].created_date'), '["%Y-%m-%d %H:%i:%s"]') AS created_date;

结果

| created_date        |
| ------------------- |
| 2018-01-17 15:51:23 |

View on DB Fiddle