Page 2 of 2
JSON Parser
Posted: Thu Dec 08, 2022 12:54 pm
by SHirsch
Hi,
if your json is an object ( {..} ) then
VAR Invoice := (STRING)o:SelectToken("HIREINV")
https://www.newtonsoft.com/json/help/ht ... tToken.htm
Stefan
JSON Parser
Posted: Fri Dec 09, 2022 6:43 am
by Sherlock
I wrote a JSON to import to DBSERVER,, way back.. hope this helps someone
METHOD AppendJSON( oFSSource ) CLASS DBServer
LOCAL lRetCode := FALSE AS LOGIC
LOCAL cSource AS STRING
LOCAL cPath AS STRING
LOCAL oError AS USUAL
LOCAL dwCurrentWorkArea AS DWORD
LOCAL jp AS JsonParser
LOCAL ja AS JsonArray
LOCAL jo AS JsonObject
LOCAL aDBStruct := {} AS ARRAY
LOCAL aAllkeys := {} AS ARRAY
LOCAL xx, yy, nPos AS DWORD
LOCAL uValue AS USUAL
#IFDEF __DEBUG__
DBFDebug("Entering "+__ENTITY__)
#ENDIF
//
SELF:lErrorFlag := FALSE
//
BEGIN SEQUENCE
VODBSelect( wWorkArea, @dwCurrentWorkArea )
IF SELF:Notify( NOTIFYINTENTTOMOVE )
IF IsInstanceOfUsual( oFSSource, #FileSpec )
cSource := oFSSource:FullPath
ELSE
cSource := oFSSource
IF At2( "", cSource ) == 0
cPath := SELF:oFileSpec:FullPath
cSource := SubStr3( cPath, 1, RAt2( "", cPath ) ) + cSource
ENDIF
ENDIF
//
aDBStruct := SELF:DbStruct // Get structure array from DBF
//
jp := JsonParser{}
IF jp:LoadFromFile( cPath+cSource ) // Read the JSON data, import to DBF
//
ja := jp:RootJson:ValAsArray // Get Root JSON array of records
// JSON data types to check... now match to DBF data types
// JsonType_String := 0 / JsonType_LongInt := 1 / JsonType_Real8 := 2 / JsonType_Float := 2
// JsonType_Logic := 3 / JsonType_Null := 4 / JsonType_Object := 5 / JsonType_Array := 6
//
FOR xx := 1 TO ja:Size // 1 to All Json array records...
jo := ja:GetJsonObject(xx) // Jo will be a Record, ie. "Prop_code TO "B_zzclean2" jo:Size = 42
//
IF SELF:APpend() // Append to the DBF the data from JSON data
//
aAllkeys := jo:AllKeys // All the keys is the FIELD names.
//
FOR yy := 1 TO ALen(aAllkeys)
// ? aAllkeys[yy] , jo:GetJsonValue(aAllkeys[yy]):JsonType // String = 0 / Numeric = 1,2 / Logic = 3
nPos := AScan(aDBStruct, {|a| a[DBS_NAME] == aAllkeys[yy]})
IF nPos > 0
//
DO CASE
CASE aDBStruct[nPos][ DBS_TYPE ] = "C" ; uValue := jo:GetString(aAllkeys[yy]) // DBF needs "C"har string format
CASE aDBStruct[nPos][ DBS_TYPE ] = "D" ; uValue := SToD(StrTran(jo:GetString(aAllkeys[yy]),[-])) // DBF needs "D"ate YYYY-DD-MM
CASE aDBStruct[nPos][ DBS_TYPE ] = "N" ; uValue := jo:GetFloat(aAllkeys[yy]) // DBF need "N"umeric could be float or Int style
uValue := Round( uValue, aDBStruct[nPos][ DBS_DEC ]) // Take Json value and trim to DBF field length [ie. 123.00 Round( xxx, 0) = 123
IF SLen(AsString(uValue)) > aDBStruct[nPos][ DBS_LEN ] // resolve 2.0 v 2 [ int ] // Length of "new" Json data, verse field length
uValue := 0 // If it will not fit, zero it.. else a crash.
ENDIF
CASE aDBStruct[nPos][ DBS_TYPE ] = "L" ; uValue := jo:Getlogic(aAllkeys[yy]) // DBF needs "L"ogic Json data as TRUE or FALSE
CASE aDBStruct[nPos][ DBS_TYPE ] = "M" ; uValue := jo:GetString(aAllkeys[yy]) // DBF needs "M"emo as string, we do not allow Arrays, even though memo allows
ENDCASE
//
SELF:FIELDPUT(aAllkeys[yy], uValue ) // Have FIELD NAME and value... write it.
ENDIF
//
NEXT
ENDIF
NEXT
SELF:Commit() // Commit the full record the say 42 fields..
SELF:Unlock() // Unlock that record, the APPEND locked it.
//
lRetCode := TRUE
ENDIF
IF lRetCode
lRetCode := SELF:__ProcessConcurrency( TRUE )
ENDIF
SELF:Notify( NOTIFYFILECHANGE )
siSelectionStatus := DBSELECTIONNULL
ELSE
lRetCode := FALSE
SELF:__SetStatusHL ( #AppendSDF, __CavoStr( __CAVOSTR_DBFCLASS_INTENTTOMOVE_CAPTION ), ;
__CavoStr( __CAVOSTR_DBFCLASS_INTENTTOMOVE ) )
ENDIF
__DBSSetSelect( dwCurrentWorkArea ) //SE-060527
//
RECOVER USING oError
oHLStatus := SELF:__GenerateStatusHL( oError )
oErrorInfo := oError
//
SELF:__ProcessConcurrency( FALSE )
//
__DBSSetSelect( dwCurrentWorkArea ) //SE-060527
lRetCode := FALSE
//
END SEQUENCE
//
#IFDEF __DEBUG__
DBFDebug("Leaving "+__ENTITY__, AsString(lRetCode))
#ENDIF
//
RETURN lRetCode
JSON Parser
Posted: Sun Dec 11, 2022 3:26 am
by jonhn
I still can't get this - sorry to ask again. I end up going in circles trying different possibilities and doubling back on myself.
My JSON string is below, and for testing I load it into an MLE text field and then try parsing it in different ways.
Method 1)
VAR o:= JArray.Parse(SELF:oDCMLE1:TextValue)
VAR Invoice := (STRING)o:SelectToken("HIREINV")
*** Invoice is empty.
Method 2
VAR o := Newtonsoft.Json.Linq.JARRAY.Parse(SELF:oDCMLE1:TextValue)
VAR Invoice := (STRING)o:SelectToken("HIREINV")
*** Invoice is empty.
(Method 3~70 result in a RT error or won't compile, haha)
Is my JSON string valid for this approach?
Do you think the object is actually loaded correctly by either of the above, and the field is not being located?
Or is my "object" invalid and nothing is loaded?
I think probably it is valid, but maybe I'm not moving to the first record - so then I start to doubt that my JSON object is valid (but it checks out online)
If anyone can point out what I've got wrong here that would be great!
The other question is: If I do eventually point to the first record and find the field, how do you skip to the next record?
Here is the JSON STRING I'm testing with:
[{
"HIREINV ": "143180",
"DTSTART ": "2022-12-14T09:00:00",
"DTEND ": "2022-12-14T12:00:00",
"DELPU ": "DEL",
"CUSTOMER ": "NZMARINE",
"PEOPLE ": " 4.00",
"HOURS ": " 3.00",
"FLEXIBLE": "FALSE"
},
{
"HIREINV ": "143584",
"DTSTART ": "2022-12-14T16:00:00",
"DTEND ": "2022-12-14T18:00:00",
"DELPU ": "DEL",
"CUSTOMER ": "SEKTOR",
"PEOPLE ": " 3.00",
"HOURS ": " 2.00",
"FLEXIBLE": "FALSE"
},
{
"HIREINV ": "143799",
"DTSTART ": "2022-12-14T09:00:00",
"DTEND ": "2022-12-14T12:00:00",
"DELPU ": "DEL",
"CUSTOMER ": "NZ MARINE",
"PEOPLE ": " 4.00",
"HOURS ": " 3.00",
"FLEXIBLE": "FALSE"
},
{
"HIREINV ": "143893",
"DTSTART ": "2022-12-14T06:00:00",
"DTEND ": "2022-12-14T06:00:00",
"DELPU ": "DEL",
"CUSTOMER ": "BLADON",
"PEOPLE ": " 0.00",
"HOURS ": " 0.00",
"FLEXIBLE": "FALSE"
},
{
"HIREINV ": "143930",
"DTSTART ": "2022-12-13T09:00:00",
"DTEND ": "2022-12-13T10:00:00",
"DELPU ": "PIK",
"CUSTOMER ": "ONSLOW",
"PEOPLE ": " 1.00",
"HOURS ": " 1.00",
"FLEXIBLE": "FALSE"
},
{
"HIREINV ": "143584",
"DTSTART ": "2022-12-14T08:00:00",
"DTEND ": "2022-12-14T09:00:00",
"DELPU ": "TECH",
"CUSTOMER ": "Christmas Party",
"PEOPLE ": " 1.00",
"HOURS ": " 1.00",
"FLEXIBLE": "FALSE"
}
]
JSON Parser
Posted: Sun Dec 11, 2022 12:54 pm
by SHirsch
everything OK with you json. The access is not correct. See the following sample:
Code: Select all
FUNCTION JsonTest() AS VOID
VAR jsonTxt := "[{'HIREINV':'143180','DTSTART':'2022-12-14T09:00:00','DTEND':'2022-12-14T12:00:00','DELPU':'DEL','CUSTOMER':'NZMARINE','PEOPLE':' 4.00','HOURS':' 3.00','FLEXIBLE':'FALSE'},{'HIREINV':'143584','DTSTART':'2022-12-14T16:00:00','DTEND':'2022-12-14T18:00:00','DELPU':'DEL','CUSTOMER':'SEKTOR','PEOPLE':' 3.00','HOURS':' 2.00','FLEXIBLE':'FALSE'},{'HIREINV':'143799','DTSTART':'2022-12-14T09:00:00','DTEND':'2022-12-14T12:00:00','DELPU':'DEL','CUSTOMER':'NZ MARINE','PEOPLE':' 4.00','HOURS':' 3.00','FLEXIBLE':'FALSE'},{'HIREINV':'143893','DTSTART':'2022-12-14T06:00:00','DTEND':'2022-12-14T06:00:00','DELPU':'DEL','CUSTOMER':'BLADON','PEOPLE':' 0.00','HOURS':' 0.00','FLEXIBLE':'FALSE'},{'HIREINV':'143930','DTSTART':'2022-12-13T09:00:00','DTEND':'2022-12-13T10:00:00','DELPU':'PIK','CUSTOMER':'ONSLOW','PEOPLE':' 1.00','HOURS':' 1.00','FLEXIBLE':'FALSE'},{'HIREINV':'143584','DTSTART':'2022-12-14T08:00:00','DTEND':'2022-12-14T09:00:00','DELPU':'TECH','CUSTOMER':'Christmas Party','PEOPLE':' 1.00','HOURS':' 1.00','FLEXIBLE':'FALSE'}]"
VAR jArr := NewtonSoft.Json.Linq.JArray.Parse(jsonTxt)
//go over each array item
FOREACH jObj AS Newtonsoft.Json.Linq.JObject IN jArr
//jObj contains now one Item
VAR hireinv := (STRING)jObj:SelectToken("HIREINV")
Console.WriteLine(hireinv)
NEXT
//you could get a special item from the array
VAR secondsHireinv := (STRING) jArr:SelectToken("[1].HIREINV")
Console.WriteLine("direct access: "+secondsHireinv)
Console.WriteLine("now with a root object")
VAR jsonTxt2 := "{'data':[{'HIREINV':'143180','DTSTART':'2022-12-14T09:00:00','DTEND':'2022-12-14T12:00:00','DELPU':'DEL','CUSTOMER':'NZMARINE','PEOPLE':' 4.00','HOURS':' 3.00','FLEXIBLE':'FALSE'},{'HIREINV':'143584','DTSTART':'2022-12-14T16:00:00','DTEND':'2022-12-14T18:00:00','DELPU':'DEL','CUSTOMER':'SEKTOR','PEOPLE':' 3.00','HOURS':' 2.00','FLEXIBLE':'FALSE'},{'HIREINV':'143799','DTSTART':'2022-12-14T09:00:00','DTEND':'2022-12-14T12:00:00','DELPU':'DEL','CUSTOMER':'NZ MARINE','PEOPLE':' 4.00','HOURS':' 3.00','FLEXIBLE':'FALSE'},{'HIREINV':'143893','DTSTART':'2022-12-14T06:00:00','DTEND':'2022-12-14T06:00:00','DELPU':'DEL','CUSTOMER':'BLADON','PEOPLE':' 0.00','HOURS':' 0.00','FLEXIBLE':'FALSE'},{'HIREINV':'143930','DTSTART':'2022-12-13T09:00:00','DTEND':'2022-12-13T10:00:00','DELPU':'PIK','CUSTOMER':'ONSLOW','PEOPLE':' 1.00','HOURS':' 1.00','FLEXIBLE':'FALSE'},{'HIREINV':'143584','DTSTART':'2022-12-14T08:00:00','DTEND':'2022-12-14T09:00:00','DELPU':'TECH','CUSTOMER':'Christmas Party','PEOPLE':' 1.00','HOURS':' 1.00','FLEXIBLE':'FALSE'}]}"
VAR jRootObj := NewtonSoft.Json.Linq.JObject.Parse(jsonTxt2)
//go over each array item
FOREACH jObj AS Newtonsoft.Json.Linq.JObject IN (NewtonSoft.Json.Linq.JArray)jRootObj:SelectToken("data")
//jObj contains now one Item
VAR hireinv := (STRING)jObj:SelectToken("HIREINV")
Console.WriteLine(hireinv)
NEXT
//you could get a special item from the array
secondsHireinv := (STRING) jRootObj:SelectToken("data[1].HIREINV")
Console.WriteLine("direct access: "+secondsHireinv)
RETURN
Stefan
JSON Parser
Posted: Mon Dec 12, 2022 8:26 am
by jonhn
Stefan, Thank you for taking the trouble with all the examples- and yes, that is everything I was looking for (and some more) and it works as required.
One thing - my string was not right, and this was causing some problems. The JSON was valid, but there were additional spaces in my field names (before the ") and that was also critical to the parsing succeeding.
Thank you. Jonathan
JSON Parser
Posted: Mon Dec 12, 2022 7:03 pm
by Meinhard
Hi Jonathan,
need to add my 2 cents here too
Let's assume you put your data into a file (easier then to put it into a string in code ) named TestData.json. You need to delete the additional space in the property names first!.Then define a class like this:
PUBLIC CLASS JsonRow
CONSTRUCTOR()
PUBLIC PROPERTY HIREINV AS STRING GET SET
PUBLIC PROPERTY DTSTART AS STRING GET SET
PUBLIC PROPERTY DTEND AS STRING GET SET
PUBLIC PROPERTY DELPU AS STRING GET SET
PUBLIC PROPERTY CUSTOMER AS STRING GET SET
PUBLIC PROPERTY PEOPLE AS STRING GET SET
PUBLIC PROPERTY HOURS AS STRING GET SET
PUBLIC PROPERTY FLEXIBLE AS STRING GET SET
END CLASS
Now it is easy to deserialize the data into alist of JsonRows:
USING System.Text
USING System.IO
using System.Text.Json
using System.Text.Json.Serialization
FUNCTION Start() AS VOID STRICT
?"Hello World!"
VAR jsonData := File.ReadAllText("TestData.json")
VAR listOfRows := JsonSerializer.Deserialize<List<JsonRow>>(jsonData)
FOREACH var row in listOfRows
? row:CUSTOMER
NEXT
WAIT
RETURN
Instead of NewtonSoft I am using the System.Text.Json package here.If you don't likethe list, you can deserialize to an typed array as well :
var arrayOfJsonRow:=JsonSerializer.Deserialize<JsonRow[]>(jsonData)
Regards
Meinhard
JSON Parser
Posted: Mon Dec 12, 2022 8:17 pm
by jonhn
Brilliant, thank you! In my case with a very simple requirement this is a good option so I'll experiment with this too.
So many ways to skin a cat...
By the way - where is this function from File.ReadAllText - I see a lot of core File functions in X#, but not this one... a replacement for Memoread()?
BR, Jonathan
JSON Parser
Posted: Mon Dec 12, 2022 9:28 pm
by ic2