nested arrays in sa_rowgenerator with sp_parse_json

[Baron]

Hi, could anybody tell me whether it is possible to retrieve subelements from the below code, so that I can get 6 lines instead of 2 (so 3X Mouse and 3X Keyboard).

begin

declare MyJson long varchar;

select '{"products":

[{

"product_id": 5696214696104,

"product_name": "mouse",

"product_price": 13.5,

"product_colors":[

{"Body_Color":"Red", "Cable_Color": "Black"},

{"Body_Color":"Blue", "Cable_Color": "Black"},

{"Body_Color":"Orange", "Cable_Color": "White"}

]},

{"product_id": 5696214696350,

"product_name": "keyboard",

"product_price": 20.5,

"product_colors":[

{"Body_Color":"Red", "Cable_Color": "White"},

{"Body_Color":"Blue", "Cable_Color": "Black"},

{"Body_Color":"Orange", "Cable_Color": "White"}]}]}' into MyJson;

DROP VARIABLE IF EXISTS sql_array;

call sp_parse_json ('sql_array', MyJson);

select sql_array.products[[row_num]].product_id,

sql_array.products[[row_num]].product_name

from sa_rowgenerator(1, CARDINALITY(sql_array.products))

end;

[Baron]

more funny is that the function sp_parse_json doesn't read the element id correctly, because its value exceeds the limit of integer.

As a result, I read both products having the same id=2147483647

[Baron]

With this statement I can for example read the color values per product (for example the colors of the first product):

select sql_array.products[[1]].product_colors[[row_num]].Body_Color pbodycolor,

sql_array.products[[1]].product_colors[[row_num]].Cable_Color pcablecolor

from sa_rowgenerator(1, CARDINALITY(sql_array.products[[1]].product_colors))

[Baron]

I don't know how I can join both SQL statements, for example if I try to do something like this:

select * from

(

select row_num JsonIndex,

sql_array.products[[row_num]].product_id pid,

sql_array.products[[row_num]].product_name pname

from sa_rowgenerator(1, CARDINALITY(sql_array.products))

) T1

inner join

(

select sql_array.products[[JsonIndex2]].product_colors[[row_num]].Body_Color pbodycolor,

sql_array.products[[JsonIndex2]].product_colors[[row_num]].Cable_Color pcablecolor

from sa_rowgenerator(1, CARDINALITY(sql_array.products[[JsonIndex2]].product_colors))

) T2

on JsonIndex2 =T1.JsonIndex ;

[VolkerBarth]

Well, the parser seems to treat JSON numbers either as double or as integer whereas you would expect a bigint here. If you add a trailig ".0" to the product_id values, it will be parsed as double, avoiding the overflow but still probably undesired.

FWIW, you can check the resulting type with the exprtype() function:

    select exprtype('select sql_array.products[[1]].product_id', 1);

[Baron]

Thanks for the nice workaround, however it will not help in my case as I am getting the JSON from a foreign system (HTTP Get Response), so I don't have influence of the content (and I don't want to go literally on the JSON and start manipulating the Json Body).

[VolkerBarth]

Yes, I agree this is a bug (or an undocumented limiting feature...), I have asked that as a separate question now.

[Baron]

Do you have any idea how to solve the JOINING problem below? (for the intersected arrays)

[Baron · Accepted solution]

Now it works!!!

begin

declare MyJson long varchar;

select '{"products":

[{

"product_id": 5696214696104,

"product_name": "mouse",

"product_price": 13.5,

"product_colors":[

{"Body_Color":"Red", "Cable_Color": "Black"},

{"Body_Color":"Blue", "Cable_Color": "Black"},

{"Body_Color":"Orange", "Cable_Color": "White"}

]},

{"product_id": 5696214696350,

"product_name": "keyboard",

"product_price": 20.5,

"product_colors":[

{"Body_Color":"Red", "Cable_Color": "White"},

{"Body_Color":"Blue", "Cable_Color": "Black"},

{"Body_Color":"Orange", "Cable_Color": "White"}]}]}' into MyJson;

DROP VARIABLE IF EXISTS sql_array;

call sp_parse_json ('sql_array', MyJson);

select x.row_num JsonIndex, sql_array.products[[x.row_num]].product_id,

sql_array.products[[x.row_num]].product_name,

((sql_array.products[[JsonIndex]]).product_colors[[y.row_num]]).Body_Color,

((sql_array.products[[JsonIndex]]).product_colors[[y.row_num]]).Cable_Color

from sa_rowgenerator(1, CARDINALITY(sql_array.products)) as x,

sa_rowgenerator(1, CARDINALITY(sql_array.products[[JsonIndex]].product_colors)) as y,

end;

[Baron · Accepted solution]

Unfortunately this function sp_parse_json works not always, and only as it wants to work!!

In my above code (in the answer) it works very fine, provided that the length of the product_colors array is the same among all products!

for example the below code doesn't work (the diefference is that I only removed one color set from the product mouse)

begin

declare MyJson long varchar;

select '{"products":

[{

"product_id": 5696214696104,

"product_name": "mouse",

"product_price": 13.5,

"product_colors":[

{"Body_Color":"Red", "Cable_Color": "Black"},

{"Body_Color":"Orange", "Cable_Color": "White"}

]},

{"product_id": 5696214696350,

"product_name": "keyboard",

"product_price": 20.5,

"product_colors":[

{"Body_Color":"Red", "Cable_Color": "White"},

{"Body_Color":"Blue", "Cable_Color": "Black"},

{"Body_Color":"Orange", "Cable_Color": "White"}]}]}' into MyJson;

DROP VARIABLE IF EXISTS sql_array;

call sp_parse_json ('sql_array', MyJson);

select x.row_num JsonIndex, sql_array.products[[x.row_num]].product_id,

sql_array.products[[x.row_num]].product_name,

((sql_array.products[[JsonIndex]]).product_colors[[y.row_num]]).Body_Color,

((sql_array.products[[JsonIndex]]).product_colors[[y.row_num]]).Cable_Color

from sa_rowgenerator(1, CARDINALITY(sql_array.products)) as x,

sa_rowgenerator(1, CARDINALITY(sql_array.products[[JsonIndex]].product_colors)) as y,

end;

Is this a bug, or I became mad with this function?

[Baron · Accepted solution]

I assume then, that this function sp_parse_json is written only for hobby using and not intended to be used in productive systems.

[VolkerBarth · Accepted solution]

A bold statement. Are you sure the problem is with sp_parse_json and not with your select statement?

FWIW, telling "Doesn't work" is usually not a helpful message: Do you get an error code (so which one?) or does the result set differ from your expectations (then how?)?

[Baron · Accepted solution]

Here I have 2 Problems:

1- Resolving big Numbers of type (Bigint) -- here the problem is with the function sp_parse_json (this is a blocker).

2- Retrieving subelements from arrays with different sizes, here the problem is not with sp_parse_json, but maybe with my sql statement (or maybe with the function sa_rowgenerator)!

Could you please try these both code snippets! why the second one doesn't work?

begin

declare MyJson long varchar;

select '{"products":

[{

"product_id": 5696214696104,

"product_name": "mouse",

"product_price": 13.5,

"product_colors":[

{"Body_Color":"Red", "Cable_Color": "Black"},

{"Body_Color":"Blue", "Cable_Color": "Black"},

{"Body_Color":"Orange", "Cable_Color": "White"}

]},

{"product_id": 5696214696350,

"product_name": "keyboard",

"product_price": 20.5,

"product_colors":[

{"Body_Color":"Red", "Cable_Color": "White"},

{"Body_Color":"Blue", "Cable_Color": "Black"},

{"Body_Color":"Orange", "Cable_Color": "White"}]}]}' into MyJson;

DROP VARIABLE IF EXISTS sql_array;

call sp_parse_json ('sql_array', MyJson);

select x.row_num JsonIndex, sql_array.products[[x.row_num]].product_id,

sql_array.products[[x.row_num]].product_name,

((sql_array.products[[JsonIndex]]).product_colors[[y.row_num]]).Body_Color,

((sql_array.products[[JsonIndex]]).product_colors[[y.row_num]]).Cable_Color

from sa_rowgenerator(1, CARDINALITY(sql_array.products)) as x,

sa_rowgenerator(1, CARDINALITY(sql_array.products[[JsonIndex]].product_colors)) as y,

end;

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

begin

declare MyJson long varchar;

select '{"products":

[{

"product_id": 5696214696104,

"product_name": "mouse",

"product_price": 13.5,

"product_colors":[

{"Body_Color":"Red", "Cable_Color": "Black"},

{"Body_Color":"Orange", "Cable_Color": "White"}

]},

{"product_id": 5696214696350,

"product_name": "keyboard",

"product_price": 20.5,

"product_colors":[

{"Body_Color":"Red", "Cable_Color": "White"},

{"Body_Color":"Blue", "Cable_Color": "Black"},

{"Body_Color":"Orange", "Cable_Color": "White"}]}]}' into MyJson;

DROP VARIABLE IF EXISTS sql_array;

call sp_parse_json ('sql_array', MyJson);

select x.row_num JsonIndex, sql_array.products[[x.row_num]].product_id,

sql_array.products[[x.row_num]].product_name,

((sql_array.products[[JsonIndex]]).product_colors[[y.row_num]]).Body_Color,

((sql_array.products[[JsonIndex]]).product_colors[[y.row_num]]).Cable_Color

from sa_rowgenerator(1, CARDINALITY(sql_array.products)) as x,

sa_rowgenerator(1, CARDINALITY(sql_array.products[[JsonIndex]].product_colors)) as y,

end;

[VolkerBarth · Accepted solution]

Well, I'm no hobbyist so I can try when I find the time to...

[Baron · Accepted solution]

As for the second snippet, I get an error message as follows:

Cannot convert nvarchar to varray

SQLCODE=-157, ODBC 3 State="070006"

[VolkerBarth · Accepted solution]

This seems to be a limitation when JSON arrays have different sizes, according to Mark's response here. So I can't tell what a workaround might look like... Possibly you would need to check whether product_colors is of type "nvarchar(32767)" (in case array sizes are different) or an array (calling exprtype will fail then), and further adjust your selection then...

[Baron · Accepted solution]

Thank you.

Because of those limitations we have to look for another solution.

[VolkerBarth · Accepted solution]

FWIW, I'm trying to figure out how to work around that - but I guess this might get rather weird...

[Baron · Accepted solution]

A workaround for the problem of arrays with multiple row numbers is possible, when I read the Array element of product_colors as nvarchar, and then apply the sp_parse_json function on this varchar, the problem is that I need to make a curosr running on each product (but at the end we can still live with it).

For the other problem with bigint there is no workaround, since we don't have influence on how the function sp_parse_json works.

[VolkerBarth · Accepted solution]

Yes, you would probably need to modify the JSON beforehand by putting double commas around numeric values so they are parsed as strings, and you would need to casst them to numbers afterwards.

[Baron · Accepted solution]

Do you mean modifying the JSON as String (using REPLACE function or similar)?

This would be then a very big overhead specially if I am getting several thousands of products each with several subelements.

Actually our goal was saving resources and having better performance so that we decided to rely on the database internal functions, but after having so much workarounds then we lose our goal.

[VolkerBarth · Accepted solution]

Do you mean modifying the JSON as String (using REPLACE function or similar)?

Yes, possibly a REGEX search for 10 and more decimal digits without trailing decimal points and without enclosing commas - and sadly SQL Anywhere does not have a regep_replace.

So I agree that a different approach (or possibly calling an external JS function) might be worthwhile...

[fvestjens · Accepted solution]

Baron, can you let me know how you solved this multiple row numbers issue using a cursor? I have the same problem now and would like to know how you solved this.