将从json值提取的字符串转换为返回null的日期

fslejnso  于 2021-06-17  发布在  Mysql
关注(0)|答案(2)|浏览(494)

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

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

我想提取 created_date 从json到sql,我通过下面的查询得到它。

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

我做错什么了?有人能帮我修一下吗?
提前谢谢

ws51t4hk

ws51t4hk1#

之后返回的日期 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 |

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 |

db fiddle视图

tzcvj98z

tzcvj98z2#

您的代码还包括 " . 对代码进行以下小更改以排除它们:

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

相关问题