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;

asked 29 Sep, 11:28

Baron's gravatar image

Baron
1.3k5075114
accept rate: 33%

edited 29 Sep, 11:28

2

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

(29 Sep, 14:25) Baron
Replies hidden

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))

(30 Sep, 04:13) 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 ;

(30 Sep, 04:18) Baron
1

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);
(30 Sep, 04:24) Volker Barth

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).

(30 Sep, 04:34) Baron
1

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

(30 Sep, 05:04) Volker Barth

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

(30 Sep, 05:38) Baron
showing 3 of 7 show all flat view

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;

permanent link

answered 30 Sep, 07:58

Baron's gravatar image

Baron
1.3k5075114
accept rate: 33%

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?

(30 Sep, 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, 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, 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, 05:14) Baron
1

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

(01 Oct, 05:35) Volker Barth

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

Cannot convert nvarchar to varray

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

(01 Oct, 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, 12:23) Volker Barth
Replies hidden

Thank you.

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

(02 Oct, 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, 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, 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, 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, 05:27) Baron

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...

(02 Oct, 09:44) Volker Barth
showing 3 of 13 show all flat view
Your answer
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:

×19
×4

question asked: 29 Sep, 11:28

question was seen: 165 times

last updated: 02 Oct, 09:44