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?

asked 19 Oct, 06:29

Frank%20Vestjens's gravatar image

Frank Vestjens
1.0k283851
accept rate: 25%

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.

(19 Oct, 08:19) 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:

×26
×23

question asked: 19 Oct, 06:29

question was seen: 90 times

last updated: 19 Oct, 08:22