JSON_ARRAYAGG return value with "\"-CodePudding

I am using json_arrayagg in Oracle to create a json array from multiple rows in a table. Here are details:

create table classInfo (classId integer, category CLOB);
insert into classInfo(1, '{"category":"AAA"}');
insert into classInfo(2, '{"category":"BBB"}');
commit;


select json_arrayagg(category returning clob) from classInfo;

The output has a \ along with " as following:

["{\"category\""AAA\"}", "{\"category\":\"BBB\"}"]

How can I get ride off those \ ?

Thanks in advance!

CodePudding user response：

Use FORMAT JSON:

select json_arrayagg(category FORMAT JSON RETURNING CLOB) from classInfo;

Which outputs:

JSON_ARRAYAGG(CATEGORYFORMATJSONRETURNINGCLOB)

[{"category":"AAA"},{"category":"BBB"}]

JSON_ARRAYAGG(CATEGORYFORMATJSONRETURNINGCLOB)
[{"category":"AAA"},{"category":"BBB"}]

db<>fiddle here