-- Oracle 19c: JSON functions Updates 

SELECT
   JSON_VALUE('{"a" : "2019-01-14T09:25:26.0075"}', '$.a' RETURNING TIMESTAMP
              ERROR ON ERROR
             )   date_timestamp,
   CAST(JSON_VALUE('{"a" : "2019-01-14T09:25:26.0075"}', '$.a' RETURNING TIMESTAMP 
                   ERROR ON ERROR
                  ) AS DATE)  date_value
FROM DUAL
;


-- IS JSON function => Returns True/False
-- First three rows are valid JSON

SELECT
 qt.ref_str
FROM (
 SELECT '{ "Automobile": "Ford" }'  ref_str  FROM DUAL UNION
 SELECT '{ ''Automobile'': ''Tesla'' }'   ref_str  FROM DUAL UNION
 SELECT '{ "isAnimal": "False" }'   ref_str  FROM DUAL UNION
 SELECT 'A Random String'  ref_str FROM DUAL UNION
 SELECT NULL  ref_str FROM DUAL
) qt
WHERE  qt.ref_str IS JSON
;

SELECT
 qt.ref_str
FROM (
 SELECT '{ "Automobile": "Ford" }'  ref_str  FROM DUAL UNION
 SELECT '{ ''Automobile'': ''Tesla'' }'   ref_str  FROM DUAL UNION
 SELECT '{ "isAnimal": "False" }'   ref_str  FROM DUAL UNION
 SELECT 'A Random String'  ref_str FROM DUAL UNION
 SELECT NULL  ref_str FROM DUAL
) qt
WHERE  qt.ref_str IS JSON STRICT            -- for STRICT JSON syntax 
;


SELECT
 qt.ref_str
FROM (
 SELECT '{ "Automobile": "Ford" }'  ref_str  FROM DUAL UNION
 SELECT '{ "Automobile": "Ford" }'  ref_str  FROM DUAL UNION
 SELECT '{ ''Automobile'': ''Tesla'' }'   ref_str  FROM DUAL UNION
 SELECT '{ "isAnimal": "False" }'   ref_str  FROM DUAL UNION
 SELECT 'A Random String'  ref_str FROM DUAL UNION
 SELECT NULL  ref_str FROM DUAL
) qt
WHERE  qt.ref_str IS JSON  WITH UNIQUE KEYS
;

SELECT
 qt.ref_str
FROM (
 SELECT '{ "Automobile": "Ford" }'  ref_str  FROM DUAL UNION
 SELECT '{ "Automobile": "Ford" }'  ref_str  FROM DUAL UNION
 SELECT '{ ''Automobile'': ''Tesla'' }'   ref_str  FROM DUAL UNION
 SELECT '{ "isAnimal": "False" }'   ref_str  FROM DUAL UNION
 SELECT 'A Random String'  ref_str FROM DUAL UNION
 SELECT NULL  ref_str FROM DUAL
) qt
WHERE  qt.ref_str IS JSON  WITHOUT UNIQUE KEYS
;


-- IS NOT JSON function => Returns True/False
-- Fourth row is not valid JSON

SELECT
 qt.ref_str
FROM (
 SELECT '{ "Automobile": "Ford" }'  ref_str  FROM DUAL UNION
 SELECT '{ ''Automobile'': ''Tesla'' }'   ref_str  FROM DUAL UNION
 SELECT '{ "isAnimal": "False" }'   ref_str  FROM DUAL UNION
 SELECT 'A Random String'  ref_str FROM DUAL UNION
 SELECT NULL  ref_str FROM DUAL
) qt
WHERE  qt.ref_str IS NOT JSON
;