Trouble using json_parse on data retrieved from a sql call - Nothing should follow the root object or array

[natephillips7]

Hey guys,

I'm having trouble parsing data I'm retrieving from a database.

The call I'm making is:
"SELECT TOP 1 WITH TIES <blah blah blah> WHERE packaged_products.pro_id = :productID AND pricing_sheets.code = :priceSheetCode AND packaged_products.delivery = 'Y' ORDER BY row_number() over (partition by packaged_products.pkp_id order by pricing_rules.effective_date desc)"

I get the correct data I am looking for back and return it from the xbasic function like this:

dim rs as sql::resultset
rs = cn.resultset

dim json as c
json = rs.ToJSONObjectSyntax()
nameOfFunction = json

Here is the data I am getting:
{
"value": "{"prs_id" : "50", "code" : "2", "description" : "COF coffeename 12oz", "pkp_id" : "5655", "price_rule" : "AB", "price_amount" : "6.4", "price_percent" : null, "effective_date" : "04/26/2021 12:00:00 000 am"}\r\n{"prs_id" : "50", "code" : "2", "description" : "COF coffeename 12oz", "pkp_id" : "18", "price_rule" : "AB", "price_amount" : "200.9", "price_percent" : null, "effective_date" : "04/26/2021 12:00:00 000 am"}"
}

This is the data I want. When I go to parse this data it throws an error:

Script: json_parse() line: 53
Error parsing json: Nothing should follow the root object or array

I understand what is happening, the call is getting 2 results, which I need, but it is formatting them as 2 seperate jsons with \r\n seperating them. It seems that the results need to be in an array instead of two jsons appended by \r\n

I'm just not sure why it is doing this or how I should properly fix it. I have done plenty of sql queries using xbasic functions in the past with a lot of success, this time it seems to be fighting me. Is it because of the SELECT TOP 1 WITH TIES expecting only 1 result? Since I partition by packaged products id it allows multiple results, 1 for each unique packaged product.

Any help with this would be very appreciated! If you have any other questions let me know, I tried to give all data I thought might be helpful.

Thanks!

[alexc]

Hi Nate,
Json parse is not going to work in this situation as the data you show is a javascript object but not valid json. Why are you converting into a json object syntax and then turning into a xbasic property via json parse rather then walking the result set or using rs.data("value") or something along those lines.

[natephillips7]

Thanks for the response Alex!

I have always converted all of my sql queries into json as output because I thought that might be a more neat or best practice way of doing things. In this case it's not working, so I guess I should try just using the resultset object to do what I need to instead. I'll play with it a bit and see if I can make something work that I like. Thanks!

[CharlesParker]

I was thinking about this and wonder if you should create a view with this info in it, it might make the callback much faster and efficient. You can always bring in a primary key with it to edit the data later on if you would like using a small form.