Bug in JSON_OBJECT with FORMAT JSON

Normally, when syntax is listed as “optional” there should be no functional difference between the inclusion or omission of that syntax.

However, with the JSON_OBJECT function, the optional “FORMAT JSON” clause not only changes functionality if you include it, it actually causes the resulting json output to be invalid! This easily verified with a simple query of both forms from DUAL.

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
 Version 18.3.0.0.0
 SQL> SELECT json_object(KEY 'mykey' VALUE 'myvalue') good_results,
   2         json_object(KEY 'mykey' VALUE 'myvalue' FORMAT JSON) bad_results
   3  FROM DUAL;
 GOOD_RESULTS
 BAD_RESULTS
 {"mykey":"myvalue"}
 {"mykey":myvalue}
 SQL>

Note the bad results return the myvalue string without the required double quotes, thus making the result invalid json. Fortunately, there is a trivial work around – simply don’t use the clause.

I have published an example on Oracle’s livesql duplicating the error:
https://livesql.oracle.com/apex/livesql/s/imezq7fnvsgjgxju9lmvsns44
and submitted a bug for it on MOS.

UPDATE:

After further review of documentation and testing more variations it appears the odd functionality may be as intended for 18c and 19c, but NOT for 12c.

From the 12cR2, 18c, 19c SQL References:

12c – This clause is optional and is provided for semantic clarity.

18c – Use this optional clause to indicate that the input string is JSON, and will therefore not be quoted in the output.

19c – Use the optional clause FORMAT JSON to indicate that the input string is JSON, and will therefore not be quoted in the output.

So, it seems for 18c and 19c, leaving the quotes off may be correct. For 12c though the clause was not documented to have a functional impact, but it does. Furthermore, there is kind of quasi-deterministic functionality in that if you call the function twice with the same parameters, you will get the same results even if the two different invocations differ by the inclusion of the FORMAT JSON clause!

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> SELECT json_object(KEY 'mykey' VALUE 'myvalue') good_result,
  2         json_object(KEY 'mykey' VALUE 'myvalue' FORMAT JSON  ) bad_result
  3  FROM DUAL;
 
GOOD_RESULT
--------------------------------------------------------------------------------
BAD_RESULT
--------------------------------------------------------------------------------
{"mykey":myvalue}
{"mykey":myvalue}

 
 
SQL> SELECT json_object(KEY 'mykey' VALUE 'myvalue' FORMAT JSON ) good_result,
  2         json_object(KEY 'mykey' VALUE 'myvalue'  ) bad_result
  3  FROM DUAL;

GOOD_RESULT
-------------------------------------------------------------------------
BAD_RESULT
--------------------------------------------------------------------------------
{"mykey":"myvalue"}
{"mykey":"myvalue"}

Note, in both queries the returned results are identical. However, the inclusion or exclusion of the quotes seems to be determined by the form of the last invocation of the json_object function. This can’t be the intended effect. So there does still appear to be a bug, but not as I originally interpreted and described it.