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;

select array_agg(row( Father,GetChildRows(Id)) order by Id) into array_Parents from tbl_Parent;

select array_Parents as Parents for json raw;

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;

select array_agg(row( Father,GetChildRows(Id)) order by Father) into array_Parents from tbl_Parent;

select array_Parents as Parents for json raw;

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?

asked 29 Dec '21, 05:41

Frank's gravatar image

Frank
826253249
accept rate: 25%

edited 30 Dec '21, 12:33

Volker%20Barth's gravatar image

Volker Barth
38.6k351525797

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:

select cardinality(array_Parents),
    cardinality(array_Parents[[1]].Children),
    cardinality(array_Parents[[2]].Children);


As to bug reporting: AFAIK you can do so via your support contract, or use this forum to do so.

(30 Dec '21, 12:26) Volker Barth
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×202
×43
×24

question asked: 29 Dec '21, 05:41

question was seen: 189 times

last updated: 30 Dec '21, 12:33