JSON_ARRAYAGG return value with "\"

Question

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!

What interface are you using to run your query? I don't see the slashes on my system; I suspect they are not added by Oracle database, but by your client interface. — , Jun 20 '22 at 20:57
Other than that - when you post data for us for testing (which is a good thing!), please test those statements yourself first, and fix any errors you find. Your INSERT statements are missing the VALUES keyword. That doesn't add to credibility! — , Jun 20 '22 at 20:59
Does this answer your question? [Remove escape characters from JSON\_OBJECT output](https://stackoverflow.com/questions/65291256/remove-escape-characters-from-json-object-output) — astentx, Jun 20 '22 at 22:06

score 2 · Accepted Answer · answered Jun 20 '22 at 19:48

2

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

answered Jun 20 '22 at 19:48

MT0

143,790
11
59
117

JSON_ARRAYAGG return value with "\"

1 Answers1