A simple reminder about how JSON PL/SQL methods deal with JSON values, it easy to get confused when you mix up JSON objects and their serialized counterparts, especially if some if these parts are coming from JSON SQL functions and you need to combine them with other parts generated by PL/SQL.
The code below should clarify the difference between a "real" JSON value and its textual representation, especially when you are assembling a JSON object with values containing other JSON objects or their serialized representation.
When you PUT a serialized JSON string into a new JSON, the method escapes all the special characters that otherwise would break the syntax (lines 10-16).
In order to reconstruct a valid JSON string, something that you can PARSE as JSON, you need to retrieve the value with GET_STRING or GET_CLOB if it is large (lines 18-20).
If you need to include a serialized JSON object into a new JSON object thus avoiding the automatic escaping, then you need to convert the serialized JSON string into a proper JSON object and then PUT it inside the new object (lines 26-29).
declare
c varchar2(255) := '{"key": 1, "value": "X"}';
d varchar2(255);
j json_object_t;
j1 json_object_t;
j2 json_object_t;
begin
if c is json then
dbms_output.put_line('c is a string containing a valid JSON');
j := json_object_t.parse(c);
j1 := new json_object_t;
j1.put('document', c);
dbms_output.put_line('c is now escaped and becomes a string literal value');
dbms_output.put_line(j1.to_string());
dbms_output.new_line;
d := j1.get_string('document');
dbms_output.put_line('c is converted back into the original JSON object string');
dbms_output.put_line(d);
dbms_output.new_line;
dbms_output.new_line;
j2 := new json_object_t;
j2.put('document', j);
dbms_output.put_line('c is still a json object value, now embedded in a new JSON object');
dbms_output.put_line(j2.to_string());
else
dbms_output.put_line('NOT JSON');
end if;
end;
/Watch out for NULL values because GET_STRING and GET_CLOB show two different behaviors in older releases of Oracle 19c.
