Assume I have the following to tables with data create or replace table tbl_Parent (Id integer default autoincrement,Father varchar(64)); create or replace table tbl_Child (Id integer default autoincrement,ParentId integer,Name varchar(64),Gender varchar(8),Age integer); insert into tbl_Parent(Father) values ('Peter Tosh'); insert into tbl_Parent(Father) values ('Jim Carrey'); insert into tbl_Child (ParentId,Name,Gender,Age) values(1,'Jawara' ,'Male' ,40); insert into tbl_Child (ParentId,Name,Gender,Age) values(1,'Andrew' ,'Male' ,38); insert into tbl_Child (ParentId,Name,Gender,Age) values(1,'Niambe' ,'Female',37); insert into tbl_Child (ParentId,Name,Gender,Age) values(1,'Aldrina','Female',35); insert into tbl_Child (ParentId,Name,Gender,Age) values(1,'Steve' ,'Male' ,33); I created the following domains: create domain domain_Child row( Name tbl_Child.Name%type ,Gender tbl_Child.Gender%type ,Age tbl_Child.Age%type); create domain domain_Parent row( Name tbl_Parent.Father%type ,Children array of domain_Child); I have created the following function: create or replace function GetChildRows(in in_ParentId integer) returns array(32) of domain_Child begin declare array_Child array(32) of domain_child; // select array_agg(row(Name, Gender, Age) order by Age) into variable array_child from tbl_Child where ParentId = in_ParentId; // return array_Child; end;I want to create a proper json using the following statement create or replace variable array_Parents array of domain_Parent;The following result is returned: [{"Parents": [{"Father":"Peter Tosh", "Children":[{"Name":"Steve","Gender":"Male","Age":33}, {"Name":"Aldrina","Gender":"Female","Age":35}, {"Name":"Niambe","Gender":"Female","Age":37}, {"Name":"Andrew","Gender":"Male","Age":38}, {"Name":"Jawara","Gender":"Male","Age":40} ]}, {"Father":"Jim Carrey", "Children":[]} ] }]However if I want to sort the parents on the name of the father create or replace variable array_Parents array of domain_Parent;This will result in: [{"Parents": [{"Father": "Jim Carrey", "Children": [] }, {"Father": "Peter Tosh", "Children": [null,null,null,null,null] }] }]This is probably caused by the fact that the first Children object has no result and then the array of the second object for some reason is cleared. How can I create a case to report the issue and hopefully get it solved as soon as possible? |
I wildly assume this bug is somewhat related to the shortcomings of sa_parse_json() you have also stumbled upon:
SQL Anywhere seems to go wrong when converting between JSON objects with varying structure (say, arrays of varying cardinality) and its own arrays of arrays with different cardinalities - and vice versa.
I don't know whether OR JSON EXPLICIT would solve the issue, however, it would certainly be way cumbersome.
In your two samples, I think the bug has to do with the fact that the first sample returns 5 for the first sub-array and 0 for the second, whereas in the second sample it's vice versa – which of course is correct, I just suspect that this influences the FOR XML RAW behaviour:
As to bug reporting: AFAIK you can do so via your support contract, or use this forum to do so.