I'm using OpenEdge 11.2 and am new to JSON.
Can a JSON like the one that follows be read into OpenEdge using the READ-JSON method?
I'm struggling to see how to setup a ProDataSet or Temp-Tables that will allow for this format.
Any suggestions, hints, code snips, or advice would be greatly appreciated.
{
"success": true,
"message": "Success",
"schedules": [
{
"schedule_id": 36,
"start_date": "2012-12-01",
"structure": {
"number": "8362",
"location_lat": "49.21422",
"location_lng": "-122.83763"
}
},
{
"schedule_id": 28,
"start_date": "2015-04-01",
"structure": {
"number": "5929W",
"location_lat": "49.20462",
"location_lng": "-122.76001"
}
},
{
"schedule_id": 5,
"start_date": "2016-12-01",
"structure": {
"number": "9076",
"location_lat": "49.1924",
"location_lng": "-122.7402"
}
}
]
}
You will have to parse your JSON manually as there is no way to infer Prodataset schema directly from the content you've posted.
In essence, what you have there is an complex JSON object with contains 3 elements: first being boolean, second being string and third being JsonArray.
JsonArray again contains 3 elements where the last one represent yet another JsonObject (think nested temp-tables in the Prodataset).
Here is the code that you could use to walk down the elements and parse them as you wish:
USING Progress.Json.ObjectModel.*.
DEFINE VARIABLE myParser AS ObjectModelParser NO-UNDO.
DEFINE VARIABLE myConstruct AS JsonConstruct NO-UNDO.
DEFINE VARIABLE myJsonObj AS JsonObject NO-UNDO.
DEFINE VARIABLE lReTOK AS LOGICAL NO-UNDO.
DEFINE VARIABLE hDS AS HANDLE NO-UNDO.
DEFINE VARIABLE aaa AS CHARACTER NO-UNDO.
�
myParser = NEW ObjectModelParser( ).
myConstruct = myParser:ParseFile("mainroad1.json").
myJsonObj = CAST(myConstruct, JsonObject).
�
/* If you would have Json with schema that could be inferred, */
/* then you would be able to uncomment lines bellow and */
/* directly use dynamic prodataset */
/* */
/* CREATE DATASET hDS. */
/* */
/* lRetOK = hDS:READ-JSON("JsonObject", myJsonObj, "EMPTY") . */
aaa = myJsonObj:GetJsonText("success") .
MESSAGE aaa
VIEW-AS ALERT-BOX INFO BUTTONS OK.
aaa = myJsonObj:GetJsonText("message") .
MESSAGE aaa
VIEW-AS ALERT-BOX INFO BUTTONS OK.
aaa = myJsonObj:GetJsonText("schedules") .
MESSAGE aaa
VIEW-AS ALERT-BOX INFO BUTTONS OK.
/* here you can cast remaining text into JsonArray with last */
/* element being JsonObjectx (structure) which could be futher */
/* cast into text, etc. */
I've used here character variable 'aaa' just to be able to show element's content in the MESSAGE statement, but you would normally use LONGCHAR, added error condition checks, etc.
Thanks so much - that's very helpful - it's works!
To get an even better understanding of how things could work...
If I had the means to alter how the JSON was written would it be possible, with only small changes, to make it readable for a ProDataSet?
What sort of changes would be required and what would the ProDataSet look like?
This might be a silly suggestion in your last question but if you can totally control the JSON produced and you know the dataset/temp-table structure you want, work backwards build the dataset, put some dummy data in it and dump it as JSOn with the write-json method and then it may be easier to build the JSON on the other side to match.
Just an idea.
As Molly mentioned it is possible and the easiest way would be to create Prodataset with the structure that would contain the data you need. Then you would use write-json method on Prodataset's handle and you could export it to a file, memptr, stream or an longchar variable.
If you haven't used Tools For Business Logic perspective in Developer Studio, then I'd suggest you take a look into it.
That's what I use when starting from scratch: I create new diagram, set all objects (Prodataset, temp-tables) with appropriate relationships and then mouse right-click -> 'Generate ABL' which creates all the code I need.
Following that, you populate temp-tables with the data and call hDS:WRITE-JSON (experiment with options to reduce formatting, change nesting etc.).
That will give you exactly what you want. Sometimes you can try it out using the code I've posted above : creating dynamic Prodataset and trying to marshal data into it by using read-json. If it works, then you massage the data using ABL walking dynamically through the data structure, otherwise you need to do it using Progress.Json.ObjectModel.* classes.
In either case, check OpenEdge Documentation 'Working with JSON' available here:
communities.progress.com/.../DOC-109242
and the manual itself :
Thanks again to both Sasha and Phillip for their help. At this point in time I can't modify the format of the JSON, but I can parse it now. If I find later on that I can modify the format of the JSON, then I have your suggestions on how to go about it.
Cheers!