Question Error passing JSON: expected bracket, but found brace (15358)

[Potish]

I need to write a progress program to read data from a JSON file with nested information into a dataset in Progress. I keep getting an error 'Error passing JSON: expected bracket, but found brace (15358)'. How can I update the program to get past this error. I don't have control over the creation of the JSON file.

The progress program looks as follows

define temp-table hotels
    field code         as character
    field name         as character
    field country      as character
    field zipcode      as character
    field address      as character
    field destination  as character
    field latitude     as decimal
    field longitude    as decimal
    field currencycode as character
    field nr_rooms     as integer
    field hotel_class  as integer
    field hotel_type   as integer
    field regions      as character extent 100
    field themes       as character extent 100
    field facilities   as character extent 100
    field created_at   as datetime-tz
    field updated_at   as datetime-tz
    field is_created   as character.

define temp-table images
    field code         as character
    field category     as character
    field is_created   as character
    field url          as character
    field created_at   as datetime-tz
    field updated_at   as datetime-tz
    field tag          as character.

define temp-table descriptions
    field code         as character
    field description  as character
    field created_at   as datetime-tz
    field updated_at   as datetime-tz
    field is_created   as character.

define dataset dsHotelsInfo for hotels, images, descriptions
    data-relation hotelsimages for hotels,
        images relation-fields(code, code) nested foreign-key-hidden
    data-relation hotelsdescriptions for hotels,
        descriptions relation-fields(code, code) nested foreign-key-hidden.

DEFINE VARIABLE cSourceType   AS CHARACTER NO-UNDO.
DEFINE VARIABLE cFile         AS CHARACTER NO-UNDO.
DEFINE VARIABLE cReadMode     AS CHARACTER NO-UNDO.
DEFINE VARIABLE lRetOK        AS LOGICAL   NO-UNDO.
DEFINE VARIABLE hdsHPROhotels AS HANDLE    NO-UNDO.

hdsHPROhotels = dataset dsHotelsInfo:handle.

ASSIGN  cSourceType = "file"
        cFile       = "C:\json\hotels-98.json"
        cReadMode   = "empty".
 
lRetOK = hdsHPROhotels:read-json(cSourceType, cFile, cReadMode).

A copy of the JSON file is attached.

 

[mollyfud]

Hi Potish,
Unfortunately some JSON files are just not good candidates for the READ-JSON methods. In these cases the are JSON parsing objects available that let you walk through the code and manipulate/record data where needed. Here is some really (and I mean REALLY) basic code that starts to show that idea with your JSON file:

USING Progress.Json.ObjectModel.ObjectModelParser.
USING Progress.Json.ObjectModel.JsonArray.
USING Progress.Json.ObjectModel.JsonObject.

DEFINE VARIABLE oParser     AS ObjectModelParser NO-UNDO.
DEFINE VARIABLE oJsonArray  AS JsonArray         NO-UNDO.
DEFINE VARIABLE oJsonObject AS JsonObject        NO-UNDO.

DEFINE VARIABLE iCount      AS INTEGER           NO-UNDO.
DEFINE VARIABLE iLength     AS INTEGER           NO-UNDO.
DEFINE VARIABLE cE AS CHARACTER extent NO-UNDO.

DEFINE VARIABLE lcSection   AS LONGCHAR          NO-UNDO.


oParser = NEW ObjectModelParser().
oJsonArray = CAST(oParser:ParseFile("c:\temp\hotels-98.json"), JsonArray).

iLength = oJsonArray:Length.
MESSAGE ilength
VIEW-AS ALERT-BOX.
DO iCount = 1 TO iLength:
  oJsonObject = oJsonArray:GetJsonObject(iCount).
  cE = oJsonObject:GETNAMES ().
  DEFINE VARIABLE cStuff AS CHARACTER NO-UNDO.
DEFINE VARIABLE ic AS INTEGER NO-UNDO.
do ic = 1 to extent(ce):
    cStuff = cstuff + cE[ic] + ",".
    end.
 
MESSAGE "Images are " oJsonObject:GETTYPE("images")
VIEW-AS ALERT-BOX.

MESSAGE cStuff

VIEW-AS ALERT-BOX.
DELETE OBJECT oJsonArray NO-ERROR.
DELETE OBJECT oParser    NO-ERROR.

END.
MESSAGE "done"
VIEW-AS ALERT-BOX.

I Hope this helps point you in the right direction.

UPDATE: By the way, here is a basic example of what reading a json file into a temp-table might look like. Of course, your example is much more complex:
http://bit.ly/1K9mpDL

 

[Potish]

Hi Mollyfud,
Thank you for the examples especially the one loading data to a temp-table. Gives me a good starting point.

One quick question - how would I go about accessing the nested arrays such as the images and descriptions in the JsonObject?

 

[mollyfud]

Hi Mollyfud,
Thank you for the examples especially the one loading data to a temp-table. Gives me a good starting point.

One quick question - how would I go about accessing the nested arrays such as the images and descriptions in the JsonObject?

If I ever get time, I will see if I could write the code to do it.
On the question, you can see in the code above that "oJsonObject:GETTYPE("images")" returns 5. This means that the type is a JSONArray (See article here for all the type numbers: http://bit.ly/1EZ0lEj). You can then use that array to walk through and create the required Images records.

I haven't had to do this myself, but I would image that if you had a set structure that you could safely rely on, it wouldn't be too bad to code. It would be trickier if you had to write the code to be completely dynamic.
HTH

 

[Potish]

Hello Mollyfud,
I used the information you provided earlier and put together a program that looks to be working. As it is the first time I written this kind of code they are probably ways to improve the code but for now it looks to be getting the job done. On a high level my latest code looks as follows

USING Progress.Json.ObjectModel.ObjectModelParser.
USING Progress.Json.ObjectModel.JsonArray.
USING Progress.Json.ObjectModel.JsonObject.
DEFINE VARIABLE oParser       AS ObjectModelParser NO-UNDO.
DEFINE VARIABLE ohotelsArray  AS JsonArray         NO-UNDO.
DEFINE VARIABLE ohotelsObject AS JsonObject        NO-UNDO.
DEFINE VARIABLE iCount      AS INTEGER           NO-UNDO.
DEFINE VARIABLE iCount2     AS INTEGER           NO-UNDO.
DEFINE VARIABLE iLength     AS INTEGER           NO-UNDO.
DEFINE VARIABLE cE          AS CHARACTER extent  NO-UNDO.
DEFINE VARIABLE cE2         AS CHARACTER extent  NO-UNDO.
DEFINE VARIABLE lcSection   AS LONGCHAR          NO-UNDO.

/* images */
DEFINE VARIABLE oimagesArray  AS JsonArray  NO-UNDO.
DEFINE VARIABLE oimagesObject AS JsonObject NO-UNDO.
DEFINE VARIABLE iimages       AS INTEGER    NO-UNDO.
/* regions */
DEFINE VARIABLE oregionsArray  AS JsonArray  NO-UNDO.
DEFINE VARIABLE iregions       AS INTEGER    NO-UNDO.
/* themes */
DEFINE VARIABLE othemesArray   AS JsonArray  NO-UNDO.
DEFINE VARIABLE ithemes        AS INTEGER    NO-UNDO.
/* facilities */
DEFINE VARIABLE ofacilitiesArray   AS JsonArray  NO-UNDO.
DEFINE VARIABLE ifacilities        AS INTEGER    NO-UNDO.
/* descriptions */
DEFINE VARIABLE odescriptionsArray  AS JsonArray  NO-UNDO.
DEFINE VARIABLE odescriptionsObject AS JsonObject NO-UNDO.
DEFINE VARIABLE idescriptions       AS INTEGER    NO-UNDO.

define temp-table tthotels
    field code         as character
    field name         as character.
   
define temp-table ttimages
    field code         as character
    field category     as character
    field is_created   as character .    
   
define temp-table ttdescriptions
    field code                  as character
    field description           as character
    field created_at            as datetime-tz.     
    
oParser    = NEW ObjectModelParser().
ohotelsArray = CAST(oParser:ParseFile("C:\json\hotels-8.json"), JsonArray).
iLength = ohotelsArray:Length.

DO iCount = 1 TO iLength:
    ohotelsObject = ohotelsArray:GetJsonObject(iCount).
    
    create tthotels.
    assign
        tthotels.code          = ohotelsObject:GetCharacter("code")
        tthotels.name          = ohotelsObject:GetCharacter("name").
       
    /* images */
    oimagesArray = ohotelsArray:GetJsonObject(iCount):GetJsonArray("images").
    iimages = oimagesArray:Length. 
    do iCount2 = 1 to iimages:
        oimagesObject = oimagesArray:GetJsonObject(iCount2).
       
        create ttimages.
        assign
            ttimages.code       = tthotels.code
            ttimages.category   = oimagesObject:GetCharacter("category")
            ttimages.is_created = oimagesObject:GetCharacter("is_created")           
    end.
    DELETE OBJECT oimagesArray NO-ERROR.
    DELETE OBJECT oimagesObject NO-ERROR.
     
    /* regions */
    oregionsArray = ohotelsArray:GetJsonObject(iCount):GetJsonArray("regions").
    iregions = oregionsArray:Length. 
    do iCount2 = 1 to iregions:
        tthotels.regions[iCount2] = oregionsArray:GetCharacter(iCount2).
    end.
    DELETE OBJECT oregionsArray NO-ERROR. 
   
    /* themes */
    othemesArray = ohotelsArray:GetJsonObject(iCount):GetJsonArray("themes").
    ithemes = othemesArray:Length.
    do iCount2 = 1 to ithemes:
        tthotels.themes[iCount2] = othemesArray:GetCharacter(iCount2).   
    end.
    DELETE OBJECT othemesArray NO-ERROR.
   
    /* facilities */
    ofacilitiesArray = ohotelsArray:GetJsonObject(iCount):GetJsonArray("facilities").
    ifacilities = ofacilitiesArray:Length.
    do iCount2 = 1 to ifacilities:
        tthotels.facilities[iCount2] = ofacilitiesArray:GetCharacter(iCount2).   
    end.
    DELETE OBJECT ofacilitiesArray NO-ERROR.
   
    /* descriptions */
    odescriptionsArray = ohotelsArray:GetJsonObject(iCount):GetJsonArray("descriptions").
    idescriptions = odescriptionsArray:Length. 
    do iCount2 = 1 to idescriptions:
        odescriptionsObject = odescriptionsArray:GetJsonObject(iCount2).
       
        create ttdescriptions.
        assign
            ttdescriptions.code                 = tthotels.code
            ttdescriptions.description          = odescriptionsObject:GetCharacter("description")
            ttdescriptions.created_at           = odescriptionsObject:GetDatetimeTZ("created_at").           
    end.
    DELETE OBJECT odescriptionsArray NO-ERROR.
    DELETE OBJECT odescriptionsObject NO-ERROR.       
END.

DELETE OBJECT ohotelsArray NO-ERROR.
DELETE OBJECT oParser    NO-ERROR.