JSON_EXTRACT and ISO8601/JSON style timestamp format

[steffen_check24]

We just updated to Exasol version 7.0.3, that now supports ISO8601/JSON style timestamp format with T/Z characters. 😃

It works fine with TO_TIMESTAMP() function, when I explicit define the format: 

select to_timestamp('1963-06-09T00:00:00.000Z', 'YYYY-MM-DDTHH24:MI:SS.FF3Z');

The build in functions JSON_VALUE() /JSON_EXTRACT() seem not to support the new format:

SELECT json_extract('{"birthday": "1963-06-09T00:00:00.000Z"}',
                    '$.birthday', '$.error()'
    )
                    emits (
                           date_of_birth TIMESTAMP ,
                           error_array VARCHAR(2000000));

Result:

SELECT json_extract('{"birthday": "1963-06-09"}',
'$.birthday', '$.error()'
)
emits (
date_of_birth TIMESTAMP ,
error_array VARCHAR(2000000));

 DATE_OF_BIRTH ERROR_ARRAY
"[{ ""column"" : ""DATE_OF_BIRTH"", ""error"" : ""cast error"" }]"

Does anyone know, if it is possible to use the JSON_EXTRACT function with the new timestamp format, without an additional TO_TIMSTAMP cast in an outer query?

Cheers,

Steffen

[steffen_check24]

Ok. As we read many fields from large json structures in multi million record tables, we try to avoid the subquery construction on json_extract.

Otherwise our TMP_DB_RAM tends to 💣 .

Currently we use json_extract for most of the keys and a separate json_value function for each timestamp value, where we can do the CAST within the main query.

Actually works quite good that way, performance is still very fast.

Still, just as an idea:

For some timestamp formats, the EMIT part of the json_extract offers an implicit cast of string to timestamp, e.g.

SELECT json_extract('{"birthday": "1963-06-09 00:00:00.000"}',
                                '$.birthday', '$.error()')
        EMITS ( date_of_birth TIMESTAMP,
                error_array VARCHAR(2000000))

 Maybe this implicit CAST could be extended to work as well for the ISO8601/JSON style timestamp format using the T/Z.

Or would we run into a timezone issue in that case? 

BR

Steffen

[exa-Lennart]

Hi Steffen,

in order to make the implicit cast work you need to set your NLS_TIMESTAMP_FORMAT session parameter to the corresponding timestamp format. Implicit casting will therefore only work with one timestamp layout at a time.

ALTER SESSION SET NLS_TIMESTAMP_FORMAT= 'YYYY-MM-DDTHH24:MI:SS.FF3Z';

Setting NLS_TIMESTAMP_FORMAT to the new timestamp will be possible with the next minor release.

[exa-Lennart]

Hi Steffen,

JSON_EXTRACT only supports native JSON types. Those are

• a string.
• a number.
• an object (JSON object)
• an array.
• a boolean.
• null.

according to: https://www.w3schools.com/js/js_json_datatypes.asp

As the source states:

JSON values cannot be one of the following data types:

• a function
• a date
• undefined

It's therefore not the JSON_EXTRACT function that does not supports TIMESTAMPs - JSON does not support TIMESTAMPs.

In order to solve your problem you have to cast the emitted string/VARCHAR.

 This does the trick:

WITH timestamp_subselect (ts, err) AS (
        SELECT json_extract('{"birthday": "1963-06-09T00:00:00.000Z"}',
                                '$.birthday', '$.error()')
        EMITS ( date_of_birth VARCHAR(50),
                error_array VARCHAR(2000000))
        )
SELECT TO_TIMESTAMP(ts, 'YYYY-MM-DDTHH24:MI:SS.FF3Z'),
       err
FROM timestamp_subselect;