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

JSON - Why does not this work?

 Forum

JSON - Why does not this work?

This question is not answered

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

trying to load it into a dataset usring dsh:read-json('longchar',myContent).

Getting errror 15358 - Error parsing JSON: expected brace, but found string.

All Replies
  • 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.
     
     
     

    Architect of the SmartComponent Library and WinKit

    Consultingwerk Ltd.

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

  • So a single json object can not be read into a dataset? I will have to add it into an array?
     
     
     
  • 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": ""
        }
      ]
    }
  • 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?

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

    Architect of the SmartComponent Library and WinKit

    Consultingwerk Ltd.

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

    ...

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