Say I have the following data:
create table parent (id number, name varchar(20));
create table child (id number, name varchar(20), parent_id number);
insert into parent values (1, 'Foo');
insert into child values (1, 'Bar', 1);
insert into child values (2, 'Baz', 1);
commit;
Now, say I want to retrieve the name of the parent and all his/her children as a JSON object:
{
"name": "Foo",
"children": [
{
"name": "Bar"
},
{
"name": "Baz"
}
]
}
Is there a way to generate that JSON object in Apex Rest Data Services using Source Type = Collection Query? Something like:
select parent_name name, listagg(child_name, ',') within group (order by child_name) as children from (select p.name parent_name, c.name child_name from parent p join child c on p.id=c.parent_id) group by parent_name;
except instead of listagg(), we would use some function to generate the inner JSON object ("children"). I know we can do it with PL/SQL and the apex_json procedures, but using a Collection Query would be much simpler.