oraclejson-arrayagg

JSON_ARRAYAGG return value with "\"

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!

Solution

  • Use FORMAT JSON:

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

    Which outputs:

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

    db<>fiddle here