|
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).
|
|
Now it works!!!
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)
Is this a bug, or I became mad with this function?
(30 Sep '20, 10:33)
Baron
I assume then, that this function sp_parse_json is written only for hobby using and not intended to be used in productive systems.
(01 Oct '20, 04:38)
Baron
Replies hidden
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?)?
(01 Oct '20, 04:49)
Volker Barth
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;
(01 Oct '20, 05:14)
Baron
As for the second snippet, I get an error message as follows:
(01 Oct '20, 05:39)
Baron
1
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...
(01 Oct '20, 12:23)
Volker Barth
Replies hidden
Thank you. Because of those limitations we have to look for another solution.
(02 Oct '20, 02:09)
Baron
1
FWIW, I'm trying to figure out how to work around that - but I guess this might get rather weird...
(02 Oct '20, 03:02)
Volker Barth
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.
(02 Oct '20, 03:59)
Baron
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.
(02 Oct '20, 04:13)
Volker Barth
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.
(02 Oct '20, 05:27)
Baron
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...
(02 Oct '20, 09:44)
Volker Barth
1
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.
(13 Oct '22, 06:54)
Frank Vestjens
|
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
With this statement I can for example read the color values per product (for example the colors of the first product):
I don't know how I can join both SQL statements, for example if I try to do something like this:
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);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).
Yes, I agree this is a bug (or an undocumented limiting feature...), I have asked that as a separate question now.
Do you have any idea how to solve the JOINING problem below? (for the intersected arrays)