JSON Parser

This forum is meant for questions and discussions about the X# language and tools
User avatar
SHirsch
Posts: 286
Joined: Tue Jan 30, 2018 8:23 am
Location: Germany

JSON Parser

Post 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
Sherlock
Posts: 63
Joined: Mon Sep 28, 2015 1:37 pm
Location: Australia mate... fare dikkum

JSON Parser

Post 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
Phil McGuinness
jonhn
Posts: 86
Joined: Thu Feb 01, 2018 8:04 am
Location: New Zealand

JSON Parser

Post 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"
}
]
User avatar
SHirsch
Posts: 286
Joined: Tue Jan 30, 2018 8:23 am
Location: Germany

JSON Parser

Post 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
jonhn
Posts: 86
Joined: Thu Feb 01, 2018 8:04 am
Location: New Zealand

JSON Parser

Post 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
User avatar
Meinhard
Posts: 81
Joined: Thu Oct 01, 2015 4:51 pm

JSON Parser

Post 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
jonhn
Posts: 86
Joined: Thu Feb 01, 2018 8:04 am
Location: New Zealand

JSON Parser

Post 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
ic2
Posts: 1856
Joined: Sun Feb 28, 2016 11:30 pm
Location: Holland

JSON Parser

Post by ic2 »

Post Reply