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

Question

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.

Josh · Accepted Answer · 2023-08-23 00:20:31Z

2

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;

edited Aug 23, 2023 at 0:20

answered Oct 19, 2020 at 15:34

Josh

5132 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related