I need to combine some existing json objects into a new json object. Using the syntax:
json3 := json_object('json1' value json1, 'json2' value json2); the existing json objects get wrapped in an extra set of double quotes, and the embedded double quotes get escaped. Without rebuilding the json objects (as in the fifth output), how can I accomplish this?
Thanx, D
Use the format json clause. Or if you're on 18c you can use treat ( ... as json ):
declare
json1 varchar2(200);
json2 varchar2(200);
json3 varchar2(200);
begin
json1 := json_object('1' value 1, '2' value 2, '3' value 3);
DBMS_OUTPUT.put_line (json1);
json2 := json_object('state' value 'AZ', 'car' value 'P1', 'pet' value 'Dog');
DBMS_OUTPUT.put_line (json2);
select json_object(
'json1' value json1 format json,
'json2' value json2 format json
)
into json3
from dual;
DBMS_OUTPUT.put_line (json3);
select json_object (
'json1' value treat ( json1 as json ),
'json2' value treat ( json2 as json )
)
into json3
from dual;
DBMS_OUTPUT.put_line (json3);
end;
/
{"1":1,"2":2,"3":3}
{"state":"AZ","car":"P1","pet":"Dog"}
{"json1":{"1":1,"2":2,"3":3},"json2":{"state":"AZ","car":"P1","pet":"Dog"}}
{"json1":{"1":1,"2":2,"3":3},"json2":{"state":"AZ","car":"P1","pet":"Dog"}}