Hi all!
Apparently, ORDS handles JSON arrays as simple strings , so it escapes quotes inside them.
I have the following view:
create or replace view v_persons
select prt.name
,json_arrayagg(chd.name order by chd.name) dependency
from persons prt
join persons chd on chd.parent_id = prt.parent_id
where prt.id = prt.parent_id
group by prt.name
order by prt.name
;
When I do select * from v_persons, I get the correct representation:
Name Dependency
-------- -----------------------------
Parent1 ["Parent1"]
Parent2 ["Parent2"]
Parent3 ["Child1","Child2","Parent3"]
However, when I use the view as a source for RESTful WS, ORDS escapes quotes in JSON tables and I get the following:
{
"items": [
{
"name": "Parent1",
"dependency": "[\"Parent1\"]"
},
{
"name": "Parent2",
"dependency": "[\"Parent2\"]"
},
{
"hame": "Parent3",
"dependency": "[\"Child1\",\"Child2\",\"Parent3\"]"
}
],
...
Apparently, ORDS handles JSON tables as simple strings.
How to make the result like the following?
{
"items": [
{
"name": "Parent1",
"dependency": ["Parent1"]
},
{
"name": "Parent2",
"dependency": ["Parent2"]
},
{
"name": "Parent3",
"dependency": ["Child1","Child2","Parent3"]
}
],
...
The service handller is defined like this:
begin
ORDS.DEFINE_HANDLER(
p_module_name => 'test'
,p_pattern => 'persons/'
,p_method => 'GET'
,p_source_type => ords.source_type_collection_feed
,p_items_per_page => 100
,p_mimes_allowed => ''
,p_comments => NULL
,p_source => '
select name
,dependency
from v_persons
');
commit;
end;
/
Thank you