escaping quotes in json data

[Pat Bremkamp]

I am starting to use json fields in my Mariadb database and it looks like it will be great in some circumstances, but I have a problem. When I feed that json into an Alpha json function, I have to escape the quotation marks. Easy enough to do by hand in the examples in documentation that only have two quotes, but I have hundreds.

I tried strtran() but it burps. What is the trick to change " to " everywhere?

For any of you that are interested, here is what I'm finding:
So far, we have 1.3 million inspection result records in our tables plus history logs of who made the changes, who did the inspections, the result of QC plus tables of defects found and how they were resolved. With Json, we will still have 1.3 million records, but in a smaller table and without those extra tables. We have two simple text fields in the record, one of which contains stuff like this:
[{"when":"2017-10-21 1105", "type":"O", "by":"33", "source":"E", "text":"POC approved."},
{"when":"2017-10-20 0645", "type":"O", "by":"1244", "source":"P", "text":"The orifice was changed to .05 in and is now 99 percent."},

{"when":"2017-10-12 1533", "type":"O", "by":"33", "source":"E", "text":"The burner is overfired and the design should be reviewed for safe operation."},
{"when":"2017-10-10 0906", "type":"Q", "by":"3", "source":"Q", "text":"This should be changed to a recommendation"},
{"when":"2017-10-08 1515", "type":"D", "by":"2,22", "source":"F", "text":"The burner is overfired and must be reduced for safe operation."}]

So, it contains the date, the evaluation at the time of the entry, who decided it, what department was responsible and what was found. To me, as Deon would say, this is amazing and very cool.

[Davidk]

Re: escaping quotes in json data

For example... what Alpha JSON function are you feeding this array of JSON Objects into?

[{"when":"2017-10-21 1105", "type":"O", "by":"33", "source":"E", "text":"POC approved."},
{"when":"2017-10-20 0645", "type":"O", "by":"1244", "source":"P", "text":"The orifice was changed to .05 in and is now 99 percent."},
{"when":"2017-10-12 1533", "type":"O", "by":"33", "source":"E", "text":"The burner is overfired and the design should be reviewed for safe operation."},
{"when":"2017-10-10 0906", "type":"Q", "by":"3", "source":"Q", "text":"This should be changed to a recommendation"},
{"when":"2017-10-08 1515", "type":"D", "by":"2,22", "source":"F", "text":"The burner is overfired and must be reduced for safe operation."}]

[Pat Bremkamp]

Re: escaping quotes in json data

I've tried a bunch of them, json_parse() and json_validate() for two.

I tried the jsescapequotes() function and it changed all the quotes to \x22 which gave an "unknown escape character" error.

I put my json into jslint and it said it was good.

[Davidk]

Re: escaping quotes in json data

Using json_parse() for an example, feeding your JSON string into json_parse() produces a dot variable. E.g.

dim myJSON as c = <<%str%
[{"when":"2017-10-21 1105", "type":"O", "by":"33", "source":"E", "text":"POC approved."},
{"when":"2017-10-20 0645", "type":"O", "by":"1244", "source":"P", "text":"The orifice was changed to .05 in and is now 99 percent."},
{"when":"2017-10-12 1533", "type":"O", "by":"33", "source":"E", "text":"The burner is overfired and the design should be reviewed for safe operation."},
{"when":"2017-10-10 0906", "type":"Q", "by":"3", "source":"Q", "text":"This should be changed to a recommendation"},
{"when":"2017-10-08 1515", "type":"D", "by":"2,22", "source":"F", "text":"The burner is overfired and must be reduced for safe operation."}]
%str%

dim dataJSON as p

dataJSON = json_parse(myJSON)

dataJSON[1] will look like this... {when=2017-10-21 1105,type=O,by=33,source=E,text=POC approved.}
dataJSON[1].text will have the value POC approved

dataJSON1.PNGdataJSON2.PNG

What is it you're wanting to do?

[Pat Bremkamp]

Re: escaping quotes in json data

Yes, that is what I want. As far as I can tell, we did exactly the same thing, but I got errors and you got the result I wanted. Magic Fingers!
Thank you David.

[RichCPT]

Re: escaping quotes in json data

Pat, were you able to get the results you wanted like David's?

If not, you might want to compare which AA build version you are using. Also, it could be a difference between doing a full install of AA into a new folder vs. applying just an update patch to existing AA folder.

I've noticed alpha using JSON object names written without double-quotes. The new standard says to always put the object names in double-quotes and I have been concerned about compatibility issues that might arise in some of Alpha's JSON manipulation functions because of this.

[Pat Bremkamp]

Re: escaping quotes in json data

I was using the interactive window. Looks like David was using a function or a script since he was able to use the debugger.

[GGandhi]

Re: escaping quotes in json data

I am trying to understand what you want to do, not the json part of it, that is simple.
Do you currently have table(s) that you want to convert to new table with json objects as data?
or, you already have table(s) with json and want to use it with alpha?

[Pat Bremkamp]

Re: escaping quotes in json data

We do safety inspections of industrial gas fired equipment in the oil industry for compliance with national, provincial and local regulations. This might be a food cart in Edmonton, a chemical plant in Calgary or the fire displays at a rock concert. Because of the diversity, there is no standard list of points to check, so we create a list of 200 to 300 questions out of a possible 1500 for each piece of equipment.

We currently have a history of over 7000 inspections and 1.3 million questions answered.

Collecting the data is the easy part. After that, we need to decide to shut down the equipment, let them continue with warnings about what needs fixing, recommend improvements, or pass the equipment. Some questions are controversial and our engineers discuss them with the owner's or installer's engineers and the Provincial authorities, so we need a history of what was said, who said it and what was the outcome. So, we have child tables and log files that record every change on the equipment and logs of every change to the questions and records of monitoring the fixes to the equipment up to the point of passing. Then if the equipment is changed or moved to a location with different regulations, we need to monitor and approve those also. Each of those other tables also have millions of records.

We see json as a way to record that history compactly without all the extra tables. Like a medical chart, we can see the history of the equipment over time, all in one table. This could be a huge improvement.

[GGandhi]

Re: escaping quotes in json data

excellent. but you want to do it from now on or convert what you have. and where does alpha comes in(your post#1) in your example.
(just my two cents, while extra tables are gone, the the data is actually incorporated into a single table and the related entry is kept as attributes or whatever one chooses to call the information as one one of the field)

[Pat Bremkamp]

Re: escaping quotes in json data

We are just now experimenting and starting to decide. Converting over is a 2018 project. I've created a json version of the 1.3 million record table over in our development area. It was pretty quick to do, and now we are looking at how to collect the data and create the reports. We already use a combination of alpha and stored procedures to collect and massage the data and create our reports, so this new approach will undoubtedly also be a mix alpha and stored procedures. We just have to work out the fastest and most reliable combination.