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. — user5683823
– user5683823, Commented Jun 20, 2022 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! — user5683823
– user5683823, Commented Jun 20, 2022 at 20:59
Does this answer your question? Remove escape characters from JSON_OBJECT output — astentx
– astentx, Commented Jun 20, 2022 at 22:06

MT0 · Accepted Answer · 2022-06-20 19:48:37Z

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, 2022 at 19:48

MT0

173k12 gold badges70 silver badges136 bronze badges

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

Collectives™ on Stack Overflow

JSON_ARRAYAGG return value with "\"

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related