JSON - Why does not this work? - OpenEdge General - Forum

Posted by Mike Fechner on 04-Nov-2019 16:22

In a ProDataset tables are properties of the outermost object. But a table is an array of rows. So you need to add a pair of [ ]

DEFINE VARIABLE theFileContent AS LONGCHAR NO-UNDO .

theFileContent =

'~{"Customer":[~{"Id":"45f2d341-5bec-e611-811f-70106fa9e1e1","StudentInsurance":false,"WantsPackage":true,"InsuranceUnder28":false,"CadetInsurance":false,"WantsTravelInsurance":true,"InsuranceHealth":false,"IsCollectiveSuperContentInsurance":true}]}' .

def var dsh as handle no-undo.

create dataset dsh.

dsh:READ-JSON ('longchar',theFileContent).

dsh::Customer:FIND-FIRST () .

MESSAGE dsh::Customer::Id

VIEW-AS ALERT-BOX.

Posted by Neil Treeby on 04-Nov-2019 16:25

The correct format for JSON for a dataset would be:

{"dataset":

{

"temptable1":

[{"field1": "vaue1", ...},

{"field1": "value1", ...}],

"temptable2":

[{"field2": "value2", ...},

{"field2": "value2", ...}]

}

Each temp-table in the dataset is a separate object.

The temp-table data is represented by a JSON array, with each row of a the temp-table represented by an object in the array.

Posted by goo on 04-Nov-2019 16:33

So a single json object can not be read into a dataset? I will have to add it into an array?

Posted by goo on 04-Nov-2019 17:40

Ok, but then what about this?

{

"Membership": [

{

"FPnumber": null,

"UserProfile": {

"Grade": null,

"Branch": 0,

"ServiceLocation": {

"Id": "00000000-0000-0000-0000-000000000000",

"Name": ""

},

"AccountNumber": "",

"Address": {

"AddressLine1": "",

"AddressLine2": "",

"Postalcode": "",

"City": ""

},

"Age": 0,

"Birthdate": "0001-01-01T00:00:00",

"Email": "",

"Firstname": "",

"Lastname": "",

"IsMember": false,

"Gender": 0,

"Logindate": null,

"Mobilephone": "",

"SecuirtyNumber": "",

"Union": {

"Number": null,

"Id": "00000000-0000-0000-0000-000000000000",

"Name": ""

},

"Officephone": "",

"Telephone": "",

"Region": {

"Number": null,

"Id": "00000000-0000-0000-0000-000000000000",

"Name": ""

},

"DefenseOrgModel": {

"orgLevel": 0,

"Id": "00000000-0000-0000-0000-000000000000",

"Name": ""

},

"ServiceLocationstring": null,

"DIF": null,

"Area": null,

"Departmenet": null,

"EmployeeNumber": "",

"RecruitmentPoints": null,

"Id": "45f2d341-5bec-e611-811f-70106fa9e1e1",

"Name": "Ida Testy"

},

"Product": {

"Union": null,

"ProductTypeString": null,

"ProductType": 0,

"ProductNumber": null,

"Start": "2019-11-04T17:26:42.8634022+00:00",

"Price": 0.0,

"Id": "9cae2f37-2499-e611-80e2-c4346bad50e4",

"Name": "Tjenestde"

},

"Amount": 0.0,

"StartDate": null,

"StopDate": null,

"Union": null,

"Status": 0,

"CreatedOn": "0001-01-01T00:00:00",

"Region": null,

"ServiceLocation": null,

"Id": "00c0db98-aa01-e811-8148-e0071b658e91",

"Name": ""

}

]

}

Posted by goo on 04-Nov-2019 17:51

It is probably because each jsonobject within is a one record object and not presented as an array. If so, is it only me that find it a bit weak that it can't load this type of jsonobjects?

Do I have to make my own loader?

Posted by Mike Fechner on 04-Nov-2019 17:56

You can load it into a JsonObject (based on the ObjectModelParser) and to some rearrangement there and then load the temp-table from the JsonObject.

Not really your own loader – but some sort of intermediate processor.

Posted by goo on 04-Nov-2019 18:02

I understand Mike, and of course not a my own loader, but then again I have to traverse the json object . Could you bring some light into how I can traverse a jsonObject and test if the object is a property, jsonObject or an jsonArray?

//Geir Otto

Posted by Peter Judge on 04-Nov-2019 18:34

There's code that does this in this class - github.com/.../ConfigFileLoggerBuilder.cls ; the LoadConfig method at github.com/.../ConfigFileLoggerBuilder.cls specifically.

For objects, you call the GetNames() method on the JSON object to get an array of characters. Use a LONGCHAR for this (not CHARACTER) because there's a bug when you hav ea lot of properties. They you DO loop = 1 to extent(names). You then can call object:GetType(names[loop]) to get the type and act accordingly.

For arrays, you do loop = 1 to array:Length. You then can call object:GetType(loop) to get the type and act accordingly.

Posted by egarcia on 04-Nov-2019 18:46

If you know that that you have an object with a record, then you can add the record to an array and use the expected structure for a temp-table or dataset.

Example:

USING PROGRESS.Json.ObjectModel.*.

DEFINE VARIABLE cData           AS CHARACTER            NO-UNDO.
DEFINE VARIABLE jsonParser      AS ObjectModelParser    NO-UNDO.
DEFINE VARIABLE jsonObject      AS JsonObject           NO-UNDO.
DEFINE VARIABLE jsonArray       AS JsonArray            NO-UNDO.

DEFINE TEMP-TABLE tt            NO-UNDO
    FIELD Id                    AS CHARACTER FORMAT "x(40)"
    FIELD StudentInsurance      AS LOGICAL
    FIELD WantsPackage          AS LOGICAL
    FIELD InsuranceUnder28      AS LOGICAL
    FIELD CadetInsurance        AS LOGICAL
    FIELD WantsTravelInsurance  AS LOGICAL
    FIELD InsuranceHealth       AS LOGICAL
//    FIELD IsCollectiveSuperContentInsurance AS LOGICAL
    .
    
DEFINE DATASET ds FOR tt.

cData = '~{"Customer":~{"Id":"45f2d341-5bec-e611-811f-70106fa9e1e1","StudentInsurance":false,"WantsPackage":true,"InsuranceUnder28":false,"CadetInsurance":false,"WantsTravelInsurance":true,"InsuranceHealth":false,"IsCollectiveSuperContentInsurance":true~}~}'.

jsonParser = NEW ObjectModelParser().
jsonObject = CAST(JsonParser:Parse(cData), JsonObject).

jsonArray = NEW JsonArray().
jsonArray:ADD(jsonObject:GetJsonObject("Customer")).

jsonObject = NEW JsonObject().
jsonObject:ADD("tt", jsonArray).

TEMP-TABLE tt:READ-JSON("JsonObject", jsonObject).
// DATASET ds:READ-JSON("JsonObject", jsonObject).

FOR EACH tt:
    DISPLAY tt.
END.

Posted by Robin Brown on 04-Nov-2019 19:23

Regarding Edsel's example above. READ-JSON of the character data directly will also work, if you change cData to a longclar and add SERIALIZE-NAME to the temp-table definition.

define variable cData AS longchar no-undo.

...

define temp-table tt no-undo serialize-name "Customer"

...

temp-table tt:read-json("longchar", cData).

...

Posted by goo on 05-Nov-2019 08:26

Thanks Peter, I have to say I find it strange that Progress have not implemented a check on jsonObjects and add an array if it is needed. At least add a logic that allow us to ask for such when doing the read-json method.

It is not a big deal for me to write what is needed for now, but it would have been so much easier if Progress did some part of that 😊

//Geir Otto

Posted by Peter Judge on 06-Nov-2019 14:38

Does the approach that Edsel posted above work? The help for READ-JSON says

JsonArray

A reference to a Progress.Json.ObjectModel.JsonArray object.

This JsonArray object must fit one of the valid patterns for a temp-table object. If any part of the JsonArray object does not fit one of the accepted patterns, the READ-JSON( ) method generates an error message and returns FALSE. If the JsonArray object fits an accepted pattern, but contains fields whose names do not match the existing temp-table schema, the mismatched fields are ignored.

JsonObject

A reference to a Progress.Json.ObjectModel.JsonObject object.

This JsonObject object must fit one of the valid patterns for a ProDataSet or temp-table object. If any part of the JsonObject object does not fit one of the accepted patterns, the READ-JSON( ) method generates an error message and returns FALSE. If the JsonObject object fits an accepted pattern, but contains tables whose names do not match the existing ProDataSet schema, or contains fields whose names do not match the existing temp-table schema, the mismatched tables or fields are ignored.

Which seems to say that what you want to do will work.

But the doc isn't as clear for passing in a LONGCHAR.

This thread is closed

JSON - Why does not this work?

All Replies