Construct nested JSON value in sql/json (Oracle Database)

user_2184048 0
Viewed 71

How do I construct a JSON value with a nested JSON value as a serialized string? I tried this:

SQL>  select json { 'y' : json_serialize(json('{"hello":"world"}')) } x
      from dual;  

X
--------------------------------------------------------------------
{"y":{"hello":"world"}}

But the result I want is:

{"y":"{\"hello\":\"world\"}"}

I'm using Oracle Database 20c.

serialization oracle sql json database
1 Answers

The JSON object constructor recognizes the output of json_serialize as serialized JSON and converts it to a JSON value when constructing the outer object. Use to_clob() instead:

select json { 'y' : to_clob(json('{"hello":"world"}')) } x
from dual;
user_21840480
Related