Houston, we've got a problem with JSON PUT method

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

In brief, in Oracle 12.2.0.1 there is some problem with PUT method of JSON_OBJECT_T, for some reason numbers are not recognized as such, but they are stored as scalar.
The workaround is to use JSON_OBJECT_T.PARSE in combination with JSON_OBJECT SQL function for entering numbers (see the script below).
Unfortunately, in turn, JSON_OBJECT_T.PARSE won't recognize dates and timestamps as such, but this behavior is explained in the documentation so it doesn't come as a surprise at least.
See also a previous entry regarding the curious handling of dates and timestamps.


Hopefully Oracle will fix this in a future release.

set SERVEROUTPUT ON
declare
  je     json_element_t;
  jo     json_object_t;
  keys   json_key_list;
begin
  jo := new json_object_t;

  dbms_output.put_line('PUT method doesn''t store "value" as number, but as scalar');
  jo.put('a_string','year');
  jo.put('a_number',2019);   -- this is not stored as number but as scalar, casting as number doesn't fix it.
  jo.put('a_boolean', true);
  jo.put('a_date', sysdate);
  jo.put('a_timestamp', cast(systimestamp as timestamp));  -- curiously you need to cast this value to get it as timestamp

  keys := jo.get_keys;
  for j in 1..keys.count
  loop
  
    je := jo.get(keys(j));
    if je.is_string then
      dbms_output.put_line(keys(j) || ' is string');
    elsif je.is_number then
      dbms_output.put_line(keys(j) || ' is number');
    elsif je.is_date then
      dbms_output.put_line(keys(j) || ' is date');
    elsif je.is_timestamp then
      dbms_output.put_line(keys(j) || ' is timestamp');
    elsif je.is_boolean then
      dbms_output.put_line(keys(j) || ' is boolean');
    elsif je.is_scalar then
      dbms_output.put_line(keys(j) || ' is scalar');
    elsif je.is_object then
      dbms_output.put_line(keys(j) || ' is object');
    elsif je.is_array then
      dbms_output.put_line(keys(j) || ' is array');
    elsif je.is_null then
      dbms_output.put_line(keys(j) || ' is null');
    end if;
    dbms_output.put_line(jo.get(keys(j)).to_string);
  end loop;
  
  dbms_output.put_line('');
  dbms_output.put_line('workaround using PARSE method, correctly stores value as number, it doesn''t recognize a_date as date or a_timestamp as timestamp but as string (this is expected and documented however)');
  jo := json_object_t.parse(json_object('a_string' value 'year', 'a_number' value 2019, 'a_boolean' value false, 'a_date' value sysdate, 'a_timestamp' value systimestamp));

  keys := jo.get_keys;
  for j in 1..keys.count
  loop
  
    je := jo.get(keys(j));
    if je.is_string then
      dbms_output.put_line(keys(j) || ' is string');
    elsif je.is_number then
      dbms_output.put_line(keys(j) || ' is number');
    elsif je.is_date then
      dbms_output.put_line(keys(j) || ' is date');
    elsif je.is_timestamp then
      dbms_output.put_line(keys(j) || ' is timestamp');
    elsif je.is_boolean then
      dbms_output.put_line(keys(j) || ' is boolean');
    elsif je.is_scalar then
      dbms_output.put_line(keys(j) || ' is scalar');
    elsif je.is_object then
      dbms_output.put_line(keys(j) || ' is object');
    elsif je.is_array then
      dbms_output.put_line(keys(j) || ' is array');
    elsif je.is_null then
      dbms_output.put_line(keys(j) || ' is null');
    end if;
    dbms_output.put_line(jo.get(keys(j)).to_string);
  end loop;

end;
/