在JSON_TABLE COLUMNS中设置时间戳格式

Question

我在oracle表中有一些JSON：

{"orders":[{"timestamp": "2016-08-10T06:15:00.4"}]}

使用JSON_TABLE选择/创建视图：

SELECT jt.*
FROM table1
JSON_TABLE (table1.json_data, '$.orders[*]' ERROR ON ERROR
COLUMNS ( StartTime TIMESTAMP PATH '$.timestamp')) AS jt;

然而，无论我把日期/时间放在JSON中的格式是什么，我总是得到：

ORA-01830：日期格式图片在转换整个输入之前结束字符串

有没有办法格式化json，或者我缺少什么？如果我错过了一个像"；2016-08-10"；，则它将成功地创建DATE列。

Answer 1

在我的Oracle 19.6.0.0.0数据库上运行查询时，解析您的示例没有任何问题(请参阅下文(。如果您使用的是旧版本的Oracle，则应用最新的修补程序集可能会有所帮助。您可能还需要将其解析为字符串，然后根据接收日期的格式使用TO_DATE。

SQL> SELECT jt.*
2    FROM (SELECT '{"orders":[{"timestamp": "2016-08-10T06:15:00.4"}]}' AS json_data FROM DUAL) table1,
3         JSON_TABLE (table1.json_data,
4                     '$.orders[*]'
5                     ERROR ON ERROR
6                     COLUMNS (StartTime TIMESTAMP PATH '$.timestamp')) AS jt;

STARTTIME
__________________________________
10-AUG-16 06.15.00.400000000 AM

Answer 2

在Oracle 18c中，您的查询也可以工作(如果添加CROSS JOIN、CROSS APPLY或逗号，表示table1之后的传统交叉联接(，并将$.timeStamp更改为小写。

但是，如果您不能在Oracle12c中使其工作，那么您可以获得字符串值并使用TO_TIMESTAMP将其转换为：

SELECT StartTime,
TO_TIMESTAMP( StartTime_Str, 'YYYY-MM-DD"T"HH24:MI:SS.FF9' )
AS StartTime_FromStr
FROM   table1
CROSS JOIN
JSON_TABLE(
table1.json_data,
'$.orders[*]'
ERROR ON ERROR
COLUMNS (
StartTime     TIMESTAMP    PATH '$.timestamp',
StartTime_Str VARCHAR2(30) PATH '$.timestamp'
)
) jt;

因此，对于您的样本数据：

CREATE TABLE table1 ( json_data VARCHAR2(4000) CHECK ( json_data IS JSON ) );
INSERT INTO table1 ( json_data )
VALUES ( '{"orders":[{"timestamp": "2016-08-10T06:15:00.4"}]}' );

该输出：

STARTTIME|STARTTIME_FROMSTR：------------------------|：---------------------------2016年8月10日06时15分40秒0000 | 2016年8日06时30分40秒0000000

db<gt；小提琴这里