SRP_JSON objects within an array

BarryStevens · September 2016

First off, excuse any non JSON(or screwed up) terminology.
This is related to http frameworks http_json_services(in case anyone has made changes to do this)

I have being returned in a json format, amv array fields.
eg:
"item_code": [
"0251",
"1001"
],
"item_description": [
"Professional Service Fee",
"Coffin Allowance"
],
"item_price": [
2900,
480
],
"item_qty": [
1,
1
]

The web developer requires them to be returned as such - 'quoteitems' will be the AMV name

"quoteitems": [
{
"item_code": "0251",
"item_description": "Professional Service Fee",
"item_price": 2900,
"item_qty": 1
},
{
"item_code": "1001",
"item_description": "Coffin Allowance",
"item_price": 480,
"item_qty": 1
},
]

What srp_json commands and parameters do I need to be able to do this?

KevinFournier · September 2016

The rest of the world doesn't appreciate associate arrays like the PICK world does. Here's the code to build your sample. Hopefully it points you in the right direction:

// Create the top level object itself, which will contain the array
If SRP_JSON(hRoot, "NEW") then
 
    // Create the array of quotes
    If SRP_JSON(hArray, "NEW", "ARRAY") then
 
        // Add the first item
        If SRP_JSON(hSingleQuote, "NEW")
 
            // Set the fields on this quote
            SRP_JSON(hSingleQuote, "SETVALUE", "item_code", "0251", "STRING")
            SRP_JSON(hSingleQuote, "SETVALUE", "item_description", "Professional Service Fee", "STRING")
            SRP_JSON(hSingleQuote, "SETVALUE", "item_price", 2900, "NUMBER")
            SRP_JSON(hSingleQuote, "SETVALUE", "item_qty", 1, "NUMBER")
 
            // Add this single quote to the array we created above
            SRP_JSON(hArray, "ADD", hSingleQuote)
 
            // All done with this single quote
            SRP_JSON(hSingleQuote, "RELEASE")
 
        end
 
        // Add the second item
        If SRP_JSON(hSingleQuote, "NEW")
 
            // Set the fields on this quote
            SRP_JSON(hSingleQuote, "SETVALUE", "item_code", "1001", "STRING")
            SRP_JSON(hSingleQuote, "SETVALUE", "item_description", "Coffin Allowance", "STRING")
            SRP_JSON(hSingleQuote, "SETVALUE", "item_price", 480, "NUMBER")
            SRP_JSON(hSingleQuote, "SETVALUE", "item_qty", 1, "NUMBER")
 
            // Add this single quote to the array we created above
            SRP_JSON(hArray, "ADD", hSingleQuote)
 
            // All done with this single quote
            SRP_JSON(hSingleQuote, "RELEASE")
 
        end
 
        // Add this array to the root as a field named "quoteitem"
        SRP_JSON(hRoot, "SET", "quoteitem", hArray)
 
        // All done with the array
        SRP_JSON(hArray, "RELEASE")
 
    end
 
    // Get the JSON
    JSON = SRP_JSON(hRoot, "STRINGIFY")
 
    // All done with the root object
    SRP_JSON(hRoot, "RELEASE")
 
end

BarryStevens · September 2016

Thank you Kevin

BarryStevens · September 2016

OK, just seeing how far I can go with this.

How would you use SRP_JSON to extract what was created from this output.

KevinFournier · September 2016

Here's how I would parse this json sample back into a delimited list. This assumes the json text is in a variable called JSON:

// Parse the JSON. The result will be "" if successful or an error if not
Result = SRP_JSON(hRoot, "PARSE", JSON)
Succeeded = Result EQ ""
If Succeeded then
 
    // Get the array
    hArray = SRP_JSON(hRoot, "GET", "quoteitem")
    If hArray then
 
        // Let's loop through the array
        Quotes = ""
        NumItems = SRP_JSON(hArray, "GETCOUNT")
        For i = 1 to NumItems
 
            // Get the current quote
            hSingleQuote = SRP_JSON(hArray, "GET", i)
            If hSingleQuote then
 
                // Extract each field of the quote
                ItemCode  = SRP_JSON(hSingleQuote, "GETVALUE", "item_code")
                ItemDesc  = SRP_JSON(hSingleQuote, "GETVALUE", "item_description")
                ItemPrice = SRP_JSON(hSingleQuote, "GETVALUE", "item_price")
                ItemQty   = SRP_JSON(hSingleQuote, "GETVALUE", "item_qty")
 
                // Add the data to our OI variable
                Quotes<-1> = ItemCode:@VM:ItemDesc:@VM:ItemPrice:@VM:ItemQty
 
                // All done with the this quote
                SRP_JSON(hSingleQuote, "RELEASE")
 
            end
 
        Next i
 
        // All done with the array
        SRP_JSON(hArray, "RELEASE")
 
    end
 
    // All done with the parsed JSON
    SRP_JSON(hRoot, "RELEASE")
 
end

BarryStevens · September 2016

Thanks, but I would be looking for something a bit more generic. ie. dont know what the array names would be (could build from dict AMVs I guess), don't know what the field names would be, can that be dynamic or would I need to get those from the dict as well, even though all amv fields for the amv id may not be returned (what then).

Anyway, don't go to any trouble at the moment as I am still having problems with the first lot retro-fitting it into Don's amv processing in the http_json_service.
Just seeing if I could do it while waiting for the client to consider paying Don to do this for me.

BarryStevens · September 2016

I realise that I could pay Don to do it and charge as if I did it, but this is a bit of a grey area with the client at the moment who don't see why they should be paying any more money for this.

KevinFournier · September 2016

Yes, you can examine an object's named members using the GETMEMBERS service. Anything you want to learn at runtime you can do using SRP_JSON.

BarryStevens · September 2016

Mmm, that definitely shows up me getting my head around the terminology.

KevinFournier · September 2016

json.org

BarryStevens · September 2016

What, you mean I have to learn stuff at my age LOL

BarryStevens · September 2016

Thanks, that looks VERY helpful

MattCrozier · September 2016

Barry - I've already had a go at this. The added code to HTTP_JSON_Services() to do the work. Let me know if you're interested - I'll pass it on.

Cheers, M@

BarryStevens · September 2016

Matt, I would be interested in seeing that, I have had a go at the "GET" and it is working, but it was a bit hit&miss so am a bit worried about my changes breaking something.
Haven't looked at the POST yet, not that confident.
My email is barry@bsbsoft.com.au

thank you

BarryStevens · September 2016

Matt,

I think the POST changed would need to be in http_resource_services

MattCrozier · September 2016

Ah right - I've only considered the GET at this stage too. POST is another beast altogether ;). Anyway, I've sent you what I've done FYI.

Cheers, M@

BarryStevens · September 2016

I am having trouble getting the members of an object array, probably because I don't fully understand the terminologies yet.

here is an extract of my json:

"quote_type_id": "PN ADULT NSNA",
    "quoteitems": [
      {
        "item_code": "0251",
        "item_complimentary": false,
        "item_description": "Professional Service Fee",
        "item_ext_price": 2900,
        "item_group": "SERVICE EXPENSES",
        "item_gst_price": 2900,
        "item_optional": false,
        "item_prepaid": false,
        "item_qty": 1
      },
      {
        "item_code": "1020",
        "item_complimentary": false,
        "item_description": "Budget Coffin",
        "item_ext_price": 480,
        "item_group": "SERVICE EXPENSES",
        "item_gst_price": 480,
        "item_optional": false,
        "item_prepaid": false,
        "item_qty": 1
      },
      {
        "item_code": "0350",
        "item_complimentary": false,
        "item_description": "Mortuary Preparation for Viewing",
        "item_ext_price": 0,
        "item_group": "SERVICE EXPENSES",
        "item_gst_price": 350,
        "item_optional": false,
        "item_prepaid": false,
        "item_qty": 0
      }
      ]

here is the existing relevant code from http_resource_services:

Body = HTTP_Services('DecodePercentString', Body)
hBody=null$
ParseResponse   = SRP_JSON(hBody, 'PARSE', Body)
If Len(ParseResponse) EQ 0 then
    // Go through the column names in the JSON object and map the column values to the respective
    // field positions in the database row.
    Fields          = Xlate('DICT.' : TableName, '%FIELDS%', '', 'X')
    NumColumns      = SRP_JSON(hBody, 'GETCOUNT')
    ColumnNames     = SRP_JSON(hBody, 'GETMEMBERS')
    For ColumnCnt = 1 to NumColumns
        ColumnName  = ColumnNames<ColumnCnt>
        hColumn     = SRP_JSON(hBody, 'GET', ColumnName)
        JSONType    = SRP_JSON(hColumn, 'TYPE')
        *if columnname _eqc 'clergy_code' then debug
        Begin Case
            Case JSONType _EQC 'Array'
                // Count the number of members in the array then loop through to build the @VM value list.
                ColumnValue = ''
                NumValues   = SRP_JSON(hColumn, 'GETCOUNT')
 
 
                ItemMembers=SRP_JSON(hColumn, 'GETMEMBERS')
                if len(ItemMembers) > 0 then
                    //do object array stuff//
 
                 
                end else
                    For ValueCnt = 1 to NumValues
                        ColumnValue := SRP_JSON(hBody, 'GETVALUE', ColumnName : '[' : ValueCnt : ']', '') : @VM
                    Next ValueCnt
                    ColumnValue[-1, 1] = ''
                    GoSub Update_ItemRec
                end
    

I am trying to add the

ItemMembers=SRP_JSON(hColumn, 'GETMEMBERS')
if len(ItemMembers) > 0 then
//do object array stuff//

but getting nowhere.

Can anyone help me out here, I need to extract the fieldname and the value for each of the array's objects(hope that new found terminology is correct).

tia

-b

MattCrozier · September 2016

Well, here's my attempt ;). I think you'd need to obtain the whole AMv first, then update the ItemRec. It should work for both AMv sets and single MV fields.

Begin Case
    Case JSONType _EQC 'Array'
        // Count the number of members in the array then loop through to build the @VM value list.
        SaveColumnName =  ColumnName
        AMvColumnNames = ""
        AMvColumnValues = ""
         
        NumRows   = SRP_JSON(hColumn, 'GETCOUNT')
        For RowCnt = 1 to NumRows
            hRowValue = SRP_JSON( hColumn, 'GET', ColumnName: '[': RowCnt: ']')
            RowType = SRP_JSON( hRowValue, 'TYPE')
            If RowType _eqc "Object" then
                RowColumnNames = SRP_JSON( hRowValue, 'GETMEMBERS')
            end else
                RowColumnNames = ColumnName
            End
                 
            NumRowColumns = dCount( RowColumnNames, @fm)
            For RowColumnCnt = 1 to NumRowColumns
                ColumnName = RowColumnNames< RowColumnCnt>
                Locate ColumnName in AMvColumnNames using @fm setting ColumnPos else
                    AMvColumnNames< ColumnPos> = ColumnName
                End
                AMvColumnValues< ColumnPos, RowCnt> = SRP_JSON( hRowValue, 'GETVALUE', ColumnName)
            Next RowColumnCnt
        Next RowCnt
         
        NumAMvColumns = dCount( AMvColumnNames, @fm)
        For AMvColumnCnt = 1 to NumAMvColumns
            ColumnName = AMvColumnNames< AMvColumnCnt>
            ColumnValue = AMvColumnValues< AMvColumnCnt>
            Gosub Update_ItemRec
        Next AMvColumnCnt
                 
        Transfer SaveColumnName to ColumnName

This is straight off the top of my head - I haven't copied it from anywhere or tested it, so UAYOR ;)

HTH, M@

BarryStevens · September 2016

Awsome. I will let you know the result of the testing.

BarryStevens · September 2016

Brilliant, it worked.
Still cant work out how you are supposed to know to do that.
But, hey it works and saved me headaches.

MattCrozier · September 2016

Cool! You get to use it before I do and saves me some testing ;)

Cheers, M@

BarryStevens · April 2017

The web developer wants to POSTwith this array to clear the mv fields associated with the amv.
“cars”:[]

How can I implement this generically.

DonBakke · April 2017

Is "cars" the name of an MV field or the name of an AMV group?

BarryStevens · April 2017

an amv group

MattCrozier · April 2017

The null array would at least indicate that this is multi valued - either an MV of values or an AMV of rows. It seems this would rely on you being able to identify 'cars' as an AMV and knowing up front all the fields in the AMV group - (ie not necessarily just the ones that have data). If there are no AMV fields for 'cars' then treat as an MV field.

You could interpret a null array as an instruction to clear all rows in an AMV group, or all values in an MV field (which is really just a special case of an AMV) - as opposed to the default of adding a blank row or value (which isn't particularly useful).

Does this help??

BarryStevens · April 2017

I wasn't sure what I would be 'seeing' after it has gone through the json routines stuff', I know it wont look like “cars”:[] , so what am I looking for.

MattCrozier · April 2017

"cars":[] would be parsed as an Array with zero members. You could code this as a special case to null out the whole AMV, or MV. So the above code could be extended to something like

Begin Case
    Case JSONType _EQC 'Array'
        // Count the number of members in the array
        SaveColumnName =  ColumnName
        AMvColumnNames = ""
        AMvColumnValues = ""
          
        NumRows   = SRP_JSON(hColumn, 'GETCOUNT')
         
        If NumRows = 0 then
            * clear All rows/values
            Locate ColumnName in AMvGroupDef< 1> setting AMvGroupNum then
                * ColumnName is an AMv group.  Get the fields names in this group
                AMvColumnNames = AMvGroupDef< 2, AMvGroupNum>
                Swap @SVM with @FM in AMvColumnNames
            end else
                * ColumnName is an MV field
                AMvColumnNames = ColumnName
            End
            AMvColumnValues = ""    ;* Empty the field(s)
 
        end else
            * loop through to build the @VM value list
            For RowCnt = 1 to NumRows
                hRowValue = SRP_JSON( hColumn, 'GET', ColumnName: '[': RowCnt: ']')
                RowType = SRP_JSON( hRowValue, 'TYPE')
                If RowType _eqc "Object" then
                    RowColumnNames = SRP_JSON( hRowValue, 'GETMEMBERS')
                end else
                    RowColumnNames = ColumnName
                End
                      
                NumRowColumns = dCount( RowColumnNames, @fm)
                For RowColumnCnt = 1 to NumRowColumns
                    ColumnName = RowColumnNames< RowColumnCnt>
                    Locate ColumnName in AMvColumnNames using @fm setting ColumnPos else
                        AMvColumnNames< ColumnPos> = ColumnName
                    End
                    AMvColumnValues< ColumnPos, RowCnt> = SRP_JSON( hRowValue, 'GETVALUE', ColumnName)
                Next RowColumnCnt
            Next RowCnt
        End
              
        NumAMvColumns = dCount( AMvColumnNames, @fm)
        For AMvColumnCnt = 1 to NumAMvColumns
            ColumnName = AMvColumnNames< AMvColumnCnt>
            ColumnValue = AMvColumnValues< AMvColumnCnt>
            Gosub Update_ItemRec
        Next AMvColumnCnt
                  
        Transfer SaveColumnName to ColumnName

where AMvGroupDef is a structure that defines your AMv groups. AMvGroupDef< 1> is a list of group names (eg 'cars'), and AMvGroupDef< 2> is an aligned list of field names (SVM delimited) for each group.

HTH, M@

DonBakke · April 2017

Barry - I am curious to see an example of your JSON from a GET method. Not the whole object, just the part related to this "cars" AMV.

BarryStevens · April 2017

Matt, thanks
Don:

"cars": [
   {
     "cars_address": "17 Tarrawanna Avenue Corrimal",
     "cars_date": "25/08/2010",
     "cars_duration": "1",
     "cars_time": "03:00PM"
   },
   {
     "cars_address": "the church",
     "cars_date": "25/08/2010",
     "cars_duration": "1",
     "cars_time": "04:00PM"
   }
 ],

DonBakke · April 2017

Thanks for that. I wanted to make sure the format of the JSON was reasonable to expect "cars" : [] to clear the data. It is.

BarryStevens · April 2017

Matt,

where AMvGroupDef is a structure that defines your AMv groups. AMvGroupDef< 1> is a list of group names (eg 'cars'), and AMvGroupDef< 2> is an aligned list of field names (SVM delimited) for each group.

Do I have to build AMvGroupDef myself or do I just pull it from somewhere in %FIELD%, or do I utilize your GetMVGroupNames(I think it is yours) routine and use that to build the associated column names

SRP_JSON objects within an array

Comments