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