sp_parse_json with sub array issue

[fvestjens]

The procedure sp_ parse_ json() can't work properly with an array with a sub array with multiple elements when the number of elements differ per array element.

Assume we have the following json where every Childs array has the same number of elements in the array:

create or replace variable l_json long varchar = 
'[{"ParentId": 1
  ,"Childs":[{"Id":10},{"Id":11},{"Id":12}]}
  ,{"ParentId":2
  ,"Childs":[{"Id":20},{"Id":21},{"Id":22}]}]';

then call sp_ parse_ json('parsedJson',l_json) is working fine and the parsedJson can be used without errors.

select parsedJson[[1]].ParentId
      ,parsedJson[[2]].ParentId
      ,parsedJson[[1]].Childs[[1]].Id
      ,parsedJson[[2]].Childs[[1]].Id;

will give the proper results

However if the childs array have different elements in the array like below:

create or replace variable l_json long varchar = 
'[{"ParentId": 1
  ,"Childs":[{"Id":10},{"Id":11},{"Id":12}]}
  ,{"ParentId":2
  ,"Childs":[{"Id":20},{"Id":21}]}]';

Then call sp_ parse_ json('parsedJson',l_json) is working fine but accessing the childs array in the parsedJson will return the following error:

Could not execute statement.
Cannot convert nvarchar to varray
SQLCODE=-157, ODBC 3 State="07006"
Line 2, column 1

Because of this error we created a solution to work around this. We added a definition of the json where the child array is defined as a long varchar

create or replace variable l_Result array of row(ParentId integer
                                                ,Childs long varchar
                                                );

After parsing the json the parsed json is copied into this l_result variable

select parsedJson into l_Result;
call sp_ parse_ json('l_Child1',l_Result[[1]].Childs);
call sp_ parse_ json('l_Child2',l_Result[[2]].Childs);

And now we can access the data again, only a little bit different

select l_Result[[1]].ParentId
      ,l_Result[[2]].ParentId
      ,l_Child1[[1]].Id
      ,l_Child2[[1]].Id;

We put this change into production environment and everything seemed to be working fine. After a while we got the same error as mentioned above. After checking what could be causing this we noticed the following:

When all childs arrays have the same number of elements we should use the first option and when the childs arrays have different elements we should use the last option. Can somebody maybe explain why the last option is not working for both json formats?

[VolkerBarth]

I guess we have discussed such sp_parse_json() limitations several times in the past - and I surely still hope that the SQL Anywhere team would improve this behaviour...

Currently, you might need to use exception handling to alternatively parse into a row/array or long varchar subtype... which might work if the number of elements varies on the last level but seems awkward at least when number of elements might vary on several levels...

I'm relating to an approach similar to that one with the issue of optionally missing elements.