While researching the use of JSON in Oracle 19c I came some interesting behaviour that wasn’t immediately obvious (to me). With this post I am hoping to save you a couple of minutes scratching your head when working with JSON_TABLE(). This is Oracle 19.3.0 on Linux and I’m connecting to it using SQLcl 19.1.
Some background
As part of my JSON-support-in-Oracle research I had a good look at JSON_TABLE. Although complex at first sight, it is a lot less intimidating if you know how to use XMLTABLE :) My goal for this post is to convert a JSON document to a relational structure.
To convert JSON to (relational) rows and columns, I believe I need to use the JSON_value_column from the list of available options in JSON_TABLE‘s JSON_column_definition. Interestingly, and different from the syntax diagram for XMLTABLE(), I’m allowed to specify a JSON_value_return_type. There are quite a a few of them, and I guess the most commonly used ones are going to be varchar2, date and timestamp. This post is about the latter two, since parsing out a text string is pretty simple.
The Test Case
I created a very simple table to test the functionality:
SQL> create table t1 ( 2 id number not null, 3 json_txt clob not null, 4 constraint pk_t1 primary key (id), 5 constraint c_is_json check (json_txt is json) 6 ); Table created.
Essentially a table to hold a JSON column plus a check constraint to make sure that I’m not inserting data that isn’t JSON. Here’s the initial JSON document I started with:
SQL> select json_query(json_txt, '$' pretty error on error)
2 from t1 where id = 1;
JSON_QUERY(JSON_TXT,'$'PRETTYERRORONERROR)
------------------------------------------------------------
{
"results" :
[
{
"resultID" : 1,
"resultDate" : "13-JUN-19 08:10:00",
"details" : "none"
}
]
}
Thanks to my check constraint I know this is valid JSON. I specified the pretty keyword to make the output more readable. When developing using new(-ish) features I prefer Oracle to raise an error so I’m going with ERROR ON ERROR.
These are my NLS settings by the way, this is a system installed with the default locale set to en_US. I have added these settings for reference and they will play a role later on.
SQL> select * from nls_session_parameters 2* where regexp_like(parameter,'(DATE|TIME)'); PARAMETER VALUE ------------------------------ ------------------------------ NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_TIME_FORMAT HH24.MI.SSXFF NLS_TIMESTAMP_FORMAT DD-MON-RR HH24.MI.SSXFF NLS_TIME_TZ_FORMAT HH24.MI.SSXFF TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH24.MI.SSXFF TZR 6 rows selected.
I am now ready to start testing.
First Test
So let’s get started with the testing. My query is shown here:
SELECT
jt.*
FROM
t1,
JSON_TABLE ( t1.json_txt, '$.results[*]' ERROR ON ERROR
COLUMNS (
resID NUMBER PATH '$.resultID',
resDATE DATE PATH '$.resultDate'
)
)
AS jt
WHERE
t1.id = 1;
Not too dissimilar to using XMLTABLE, I need to pass the column containing my JSON document to the function, followed by the path to the data within the JSON document I want to parse. I could have stored multiple results in the array (and I did initially, but considered that a little too confusing), hence the reference to all of them.
The columns() clause (of type JSON_value_column) allows me to define columns, their respective data type and the path relative to the second argument to JSON_TABLE. In my case that’s $.results.resultID and $.results.resultDate. These two are mapped to resID and resDATE in the output. I am limiting the query to my first row in t1.
When I execute this query against the first record in the table, I get an error:
SQL> SELECT
2 jt.*
3 FROM
4 t1,
5 JSON_TABLE ( t1.json_txt, '$.results[*]' ERROR ON ERROR
6 COLUMNS (
7 resID NUMBER PATH '$.resultID',
8 resDATE DATE PATH '$.resultDate'
9 )
10 )
11 AS jt
12 WHERE
13 t1.id = 1;
Error starting at line : 1 in command -
SELECT
jt.*
FROM
t1,
JSON_TABLE ( t1.json_txt, '$.results[*]' ERROR ON ERROR
COLUMNS (
resid NUMBER PATH '$.resultID',
resdate DATE PATH '$.resultDate'
)
)
AS jt
WHERE
t1.id = 1
Error report -
ORA-01861: literal does not match format string
This is kind of surprising, my suspicion at the time was that something is wrong with the NLS_DATE_FORMAT settings. But whatever I tried, I always got the same error. Using MOS and other resources on the net turned out blank, which might well be down to my inadequate search efforts. Tracing didn’t really help either.
Second Test
I tried a few other things until, with the help of an educated guess, I got lucky: I removed the time portion from the resultDate and formatted it to the output you get from selecting sysdate from dual, as shown here:
SQL> select json_query(json_txt, '$.results' pretty error on error)
2 from t1 where id = 2;
JSON_QUERY(JSON_TXT,'$.RESULTS'PRETTYERRORONERROR)
------------------------------------------------------------
[
{
"resultID" : 2,
"resultDate" : "13-JUN-19",
"details" : "none"
}
]
This time around, the error message was different:
SQL> SELECT
2 jt.*
3 FROM
4 t1,
5 JSON_TABLE ( t1.json_txt, '$.results[*]' ERROR ON ERROR
6 COLUMNS (
7 resID NUMBER PATH '$.resultID',
8 resDATE DATE PATH '$.resultDate'
9 )
10 )
11 AS jt
12 WHERE
13 t1.id = 2;
Error starting at line : 1 in command -
SELECT
jt.*
FROM
t1,
JSON_TABLE ( t1.json_txt, '$.results[*]' ERROR ON ERROR
COLUMNS (
resID NUMBER PATH '$.resultID',
resDATE DATE PATH '$.resultDate'
)
)
AS jt
WHERE
t1.id = 2
Error report -
ORA-40688: date or time stamp string value does not conform
to ISO 8601 format
Aha! That’s much better than ORA-01861: literal does not match format string. So it would appear as if the date the system expected to find in the JSON document must be ISO 8601 compliant. Ummm, what exactly is an ISO 8601 date format? The Internet knew!
Third Test
So maybe using an ISO 8601 time format in the JSON document is the answer? Worth a try, the JSON document now looks like this:
SQL> select json_query(json_txt, '$.results' pretty error on error)
2 from t1 where id = 3;
JSON_QUERY(JSON_TXT,'$.RESULTS'PRETTYERRORONERROR)
------------------------------------------------------------
[
{
"resultID" : 3,
"resultDate" : "2019-06-13T16:39:00+00:00",
"details" : "none"
}
]
It seems that ISO 8601 requires a slightly more complex time expression, much more like a timestamp in Oracle. Let’s see if this works:
SQL> SELECT
2 jt.*
3 FROM
4 t1,
5 JSON_TABLE ( t1.json_txt, '$.results[*]' ERROR ON ERROR
6 COLUMNS (
7 resID NUMBER PATH '$.resultID',
8 resDATE DATE PATH '$.resultDate'
9 )
10 )
11 AS jt
12 WHERE
13 t1.id = 3;
RESID RESDATE
---------- ---------
3 13-JUN-19
Hurray, no errors, but result data for a change!
Caveat
But wait, that’s not the whole story. I have stored a time component in the input document, so how do I get that? I’d normally use to_char() for that purpose, so let’s give that a go:
SQL> SELECT 2 to_char(jt.resDATE, 'dd.mm.yyyy hh24:mi:ss') as formattedDate 3 FROM 4 t1, 5 JSON_TABLE ( t1.json_txt, '$.results[*]' ERROR ON ERROR 6 COLUMNS ( 7 resID NUMBER PATH '$.resultID', 8 resDATE DATE PATH '$.resultDate' 9 ) 10 ) 11 AS jt 12 WHERE 13 t1.id = 3; FORMATTEDDATE ------------------- 13.06.2019 00:00:00
Hmmm, that’s midnight, and not 16:39:00+00:00 …. Unless I have missed something important it seems as if the DATE column type in the columns() clause stripped the input of its time component. Not to worry though, there’s a timestamp type. Maybe that can help?
SQL> SELECT
2 jt.*
3 FROM
4 t1,
5 JSON_TABLE ( t1.json_txt, '$.results[*]' ERROR ON ERROR
6 COLUMNS (
7 resID NUMBER PATH '$.resultID',
8 resDATE TIMESTAMP PATH '$.resultDate'
9 )
10 )
11 AS jt
12 WHERE
13 t1.id = 3;
RESID RESDATE
---------- ----------------------------
3 13-JUN-19 16.39.00.000000000
Voila! I’ve got a date and time.
Summary
From my very limited testing it appears as if input date in the JSON document has to be provided in ISO 8601 format, or otherwise Oracle raises an error. Unlike in SQLLDR’s control file there appears to be no option to tell Oracle about the formatting. Changing the NLS_DATE_FORMAT (not shown here) did affect the output of the query only. It didn’t appear to be applied when parsing the input.
Martins Blog 