Oracle.ORDS: How to return column as a JSON array?

Andrew Kondrashov 0
Viewed 27

I have a master table and a detail table. I want to make a "GET" request to get something like this

select "id","name","color","etc",
(select json_arrayagg(regions returning clob) from tbl_regions r where r.id = t.id) regions from clients t;

and i want to have a json array in the result JSON. BUT I have a string attribute in a reponse from ORDS service. How to convert or to set type to a json array to a column

now i have a relust like this

{
..
..
..
"regions":"["1","2"]"
}

but i want a natural array without '"'

"regions":["1","2"]
oracle sql json arrays
2 Answers

JSON results from ORDS should be aliased "{}some_name". Without the braces the engine converts the result into an escaped string. And I would return json_object per row, it could be easier for the client instead of the mix you are trying to do.

p3consulting0

Thank you so much!!! It works!

I have to add a special alias with {}

select "id","name","color","etc",
(select json_arrayagg(regions returning clob) from tbl_regions r where r.id = t.id) "{}regions" from clients t;
Andrew Kondrashov0
Related