×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

njJson : not succeeding to get it work
3

njJson : not succeeding to get it work

njJson : not succeeding to get it work

(OP)
Hi friends,

I downloaded the nfJson library from gitHub and the provided examples PRG files are working properly.

I have a json file, from which I want to retrieve data and populate a cursor. I have seen a PRG in nfJson 'nfopenjson.prg'. The example provided for that prg works fine. However, when I try to apply that to a slightly different structured json file, I am getting 'Syntax error' message.

Below is the content of the JSON file:

CODE -->

{
  "master" : {
    "decRef" : {
      "msgTyp" : "F",
      "prtofRptng" : "INNSA1",
      "jobNo" : 12345,
      "jobDt" : "20190806",
      "rptngEvent" : "SAM",
      "mnfstNoRotnNo" : 175912,
      "mnfstDtRotnDt" : "20190716",
      "vesselTypMvmt" : "FI"
    }
  }
} 

Below is the program that I am trying:

CODE -->

lcJson = FILETOSTR('sampleValues.json')
=nfOpenJson(lcJson,'$.array', ' ;
		- msgTyp        v(1)  $.master.msgTyp ;
		- prtofRptng    v(20) $.master.prtofRptng')
BROWSE 

I am just checking with retrieving only 2 columns. But, when I run this, I am getting 'Syntax error'.
Can anyone help me to find out the problem? What I am missing?

Thank you very much in advance,
Rajesh


RE: njJson : not succeeding to get it work


Hi Rajesh,

nfOpenJson is about iterating objects inside arrays,
So we should provide an object with at least one array of
objects we want to map to the cursor we specify.

Your json returns an object. The simple solution is to wrap it
inside an array before passing it to nfOpenJson ( see sample 1 )
putting it between "[]", and in this case your array will be "unnamed",
( like the sample in github) so nfJson names it 'array',
you can work with it as "$.array".

Sample 2 and 3 name the array so you can see it clear:

CODE -->

text to lcjson noshow

[

{
  "master" : {
    "decRef" : {
      "msgTyp" : "F",
      "prtofRptng" : "INNSA1",
      "jobNo" : 12345,
      "jobDt" : "20190806",
      "rptngEvent" : "SAM",
      "mnfstNoRotnNo" : 175912,
      "mnfstDtRotnDt" : "20190716",
      "vesselTypMvmt" : "FI"
    }
  }
} 

]

endtext


=nfOpenJson( lcJson,'$.array', ' ;
    - msgTyp        v(1)  $.master.decref.msgTyp ;
    - prtofRptng    v(20) $.master.decref.prtofRptng')

BROWSE 



text to lcjson noshow
{ "myArrayofObjects": 

[

{
  "master" : {
    "decRef" : {
      "msgTyp" : "X",
      "prtofRptng" : "INNSA9",
      "jobNo" : 12345,
      "jobDt" : "20190806",
      "rptngEvent" : "SAM",
      "mnfstNoRotnNo" : 175912,
      "mnfstDtRotnDt" : "20190716",
      "vesselTypMvmt" : "FI"
    }
  }
}

]

}

endtext


=nfOpenJson( lcJson,'$.myArrayOfObjects', ' ;
    - msgTyp        v(1)  $.master.decref.msgTyp ;
    - prtofRptng    v(20) $.master.decref.prtofRptng')

BROWSE 


**** now with more rows ( array elements )

text to lcjson noshow
{ "myArrayofObjects": [

{
  "master" : {
    "decRef" : {
      "msgTyp" : "X",
      "prtofRptng" : "INNSA11",
      "jobNo" : 12345,
      "jobDt" : "20190806",
      "rptngEvent" : "SAM",
      "mnfstNoRotnNo" : 175912,
      "mnfstDtRotnDt" : "20190716",
      "vesselTypMvmt" : "FI"
    }
  }
}
,
{
  "master" : {
    "decRef" : {
      "msgTyp" : "Y",
      "prtofRptng" : "INNSA20"
    }
  }
}

]
}

endtext


=nfOpenJson( lcJson,'$.myArrayOfObjects', ' ;
    - msgTyp        v(1)  $.master.decref.msgTyp ;
    - prtofRptng    v(20) $.master.decref.prtofRptng')

BROWSE 

I'll be happy to assist on any question / feedback.



Marco Plaza
@nfoxProject
https://www.github.com/nftools

RE: njJson : not succeeding to get it work

(OP)
Hi Marco,

You're the one created this library right? Thank you so much and a big salute to you sir!
I got it worked!

When I added more columns in the list, it was giving me 'unrecognized command' and I think it was because of the string length.
So, now I changed as below. Too, I added '[' & ']' directly in the FILETOSTR function. From a more bigger json file with data of multiple tables, I am able to retrieve data of each table selectively now.

Marco, you're really awesome dear friend!!!

Below is my new code:

CODE -->

lcJson = '['+CHR(10)+CHR(13)+FILETOSTR('E:\works\SampleValues_test2.json')+CHR(10)+']'

lcSchema =	" - msgTyp v(1) $.master.decref.msgTyp " + ;
			" - prtofRptng v(20) $.master.decref.prtofRptng " + ;
			" - jobNo n(6) $.master.decref.jobNo " + ;
			" - jobDt v(8) $.master.decref.jobDt " + ;
			" - rptngEvent v(20) $.master.decref.rptngEvent " + ;
			" - mnfstNoRotnNo n(6) $.master.decref.mnfstNoRotnNo " + ;
			" - mnfstDtRotnDt v(8) $.master.decref.mnfstDtRotnDt " + ;
			" - vesselTypMvmt v(2) $.master.decref.vesselTypMvmt "

=nfOpenJson( lcJson,'$.array', lcSchema)

BROWSE 

lcSchema =	" - voyageNo v(6) $.master.voyageDtls.voyageNo " + ;
			" - cnvnceRefNmbr v(5) $.master.voyageDtls.cnvnceRefNmbr " + ;
			" - totalNoOfTrnsprtEqmtMnfsted n(3) $.master.voyageDtls.totalNoOfTrnsprtEqmtMnfsted " + ;
			" - crgoDescCdd v(1) $.master.voyageDtls.crgoDescCdd " + ;
			" - briefCrgoDesc v(25) $.master.voyageDtls.briefCrgoDesc " + ;
			" - totalNmbrOfLines n(1) $.master.voyageDtls.totalNmbrOfLines " + ;
			" - exptdDtAndTimeOfArvl v(14) $.master.voyageDtls.exptdDtAndTimeOfArvl " + ;
			" - nmbrOfPsngrsMnfsted n(1) $.master.voyageDtls.nmbrOfPsngrsMnfsted " + ;
			" - nmbrOfCrewMnfsted n(2) $.master.voyageDtls.nmbrOfCrewMnfsted " + ;
			" - shipItnrySeq v(2) $.master.voyageDtls.shipItnry.shipItnrySeq " + ;
			" - prtOfCallCdd v(5) $.master.voyageDtls.shipItnry.prtOfCallCdd " + ;
			" - prtOfCallName v(1) $.master.voyageDtls.shipItnry.prtOfCallName"

=nfOpenJson( lcJson,'$.array', lcSchema)

BROWSE 

But now, I have a query:

We have to pass the first parameter as a string. So, if my json file is considerably big with plenty of data, could that be a problem when I convert it into a string to be passed to 'nfOpenJson' function ?
(Sorry, I don't have such a big file to test this on my own at this moment)

Thank you so much Marco
and all best wishes to you for all your current & future fantastic projects like this

Rajesh

RE: njJson : not succeeding to get it work

(OP)
Hi Marco,

How can I retrieve the data of "itemDtls" from the below json. I tried all possible ways that came into my mind, but no way.
Because the parent "mastrCnsgmtDec" is an array. I can make it out from that '[' after the name.

CODE -->

[
{
  "master" : {
    "decRef" : {
      "msgTyp" : "F",
      "prtofRptng" : "INNSA1",
      "jobNo" : 12345,
      "jobDt" : "20190806",
      "rptngEvent" : "SAM",
      "mnfstNoRotnNo" : 175912,
      "mnfstDtRotnDt" : "20190716",
      "vesselTypMvmt" : "FI"
    },
    "mastrCnsgmtDec" : [ {
      "MCRef" : {
        "lineNo" : 1,
        "mstrBlNo" : "MSCUOM379746",
        "mstrBlDt" : "20190804",
        "consolidatedIndctr" : "S",
        "prevDec" : "N"
      },
      "itemDtls" : [ {
        "hsCd" : "5652",
        "crgoItemSeqNmbr" : "1",
        "crgoItemDesc" : "TEXTILE",
        "unoCd" : "ZZZZZ",
        "imdgCd" : "ZZZ",
        "nmbrOfPkgs" : "5000",
        "typOfPkgs" : "BGS"
      } ],
      "suplmntryDec" : {
        "csnSbmtdTyp" : "ASA"
      }
    } ],
  }
}
] 

lcJson variable contains the complete above json as it it.
I tried the following, both:

CODE -->

=nfOpenJson( lcJson, '$.array', ;
                    " - hsCd v(4) $.master.mastrCnsgmtDec.itemDtls.hsCd ")
=nfOpenJson( lcJson,'$.master.mastrCnsgmtDec.itemDtls', ;
                    " - hsCd v(4) $.hsCd ") 
The 1st one gives 'ITMDTLS' not found error. 2nd one gives 'Not a character expression' error.
(Please note that for trial I am just considering only one column)

What is the proper method to retrieve data of a particular item when the item is member of an array ?

Thank you in advance,
Rajesh



RE: njJson : not succeeding to get it work

Hi Rajesh, thanks for your feedback.

nfJson won't limit your string size, but remember it's intended to provide support for web services, not to move huge datasets.

Below is the syntax you need to use for the scenario that's giving you trouble, and some code hints to ease schema definition more readable ( original syntax was made to provide compatibility with MS SQL docs ).


CODE

close tables all

TEXT TO lcJson NOSHOW TEXTMERGE
[
{
  "master" : {
    "decRef" : {
      "msgTyp" : "F",
      "prtofRptng" : "INNSA1",
      "jobNo" : 12345,
      "jobDt" : "20190806",
      "rptngEvent" : "SAM",
      "mnfstNoRotnNo" : 175912,
      "mnfstDtRotnDt" : "20190716",
      "vesselTypMvmt" : "FI"
    },
    "mastrCnsgmtDec" : [ {
      "MCRef" : {
        "lineNo" : 1,
        "mstrBlNo" : "MSCUOM379746",
        "mstrBlDt" : "20190804",
        "consolidatedIndctr" : "S",
        "prevDec" : "N"
      },
      "itemDtls" : [ {
        "hsCd" : "5652",
        "crgoItemSeqNmbr" : "1",
        "crgoItemDesc" : "TEXTILE",
        "unoCd" : "ZZZZZ",
        "imdgCd" : "ZZZ",
        "nmbrOfPkgs" : "5000",
        "typOfPkgs" : "BGS"
      } ],
      "suplmntryDec" : {
        "csnSbmtdTyp" : "ASA"
      }
    }
    ]}
  }
]

ENDTEXT
* Trick:
* open your debugger and watch m.oo, expand the property tree and just 
* drag into your code / command window the property you want to get the path

public oo
oo = nfJsonRead(m.lcJson)

*
* Sample 1 using text - endtext  ( get used to type intellisense's TEXTEND ! )
*


TEXT TO lcschema NOSHOW textmerge pretext 8  && pretext 8 ( won't be neccesary in following versions )
 - msgTyp        v(1)   $.master.decref.msgTyp
 - prtofRptng    v(20)  $.master.decref.prtofRptng
 - jobNo         n(6)   $.master.decref.jobNo
 - jobDt         v(8)   $.master.decref.jobDt
 - rptngEvent    v(20)  $.master.decref.rptngEvent
 - mnfstNoRotnNo n(6)   $.master.decref.mnfstNoRotnNo
 - mnfstDtRotnDt v(8)   $.master.decref.mnfstDtRotnDt
 - vesselTypMvmt v(2)   $.master.decref.vesselTypMvmt
ENDTEXT

nfopenjson( m.lcjson,'', m.lcschema) &&  we can pass '' too if we just need the root unnamed array

Browse


*-----------------------------------------------------------------
* Note this is unadvised unless you are completely sure you only want the 1st child from each record:
*-----------------------------------------------------------------

TEXT TO lcSchema NOSHOW textmerge pretext 8
- crgoitemdesc v(4) $.master.mastrcnsgmtdec(1).itemdtls(1).crgoitemdesc
- hscd         v(4) $.master.mastrcnsgmtdec(1).itemdtls(1).hscd
ENDTEXT


nfopenjson( m.lcjson, '$.array', m.lcschema )


Browse

* the expected way would be to specify the path for the array ( as I stated on the documentation )
* but it's unsupported in current version when arrays are inside other arrays due to some vfp tricky ways
* to deal with that -but It will work in next release- :

Try

  TEXT TO lcSchema NOSHOW textmerge  pretext 8 

- crgoitemdesc v(4) $.crgoitemdesc
- hscd         v(4) $.hscd

  ENDTEXT

  nfopenjson( lcjson, '$.array[1].master.mastrcnsgmtdec[1].itemdtls', m.lcschema )

Catch

  ? 'unsupported..'

Endtry 


Marco Plaza
@nfoxProject
https://www.github.com/nftools

RE: njJson : not succeeding to get it work

(OP)
Hi Marco,

Thank you. Let me try this and I will let you know.

Rajesh

RE: njJson : not succeeding to get it work

(OP)
Hi Marco,

Apologies for my silence.

Now, I had to suspend json reading and working on generating a json from tables.
We have a schema specification. I put that into a control table which has 'Parent' and 'Child' fields along with other specifications and my program will generate json based on that control table. I am almost succeeding as far as the clients requirements.
Will get back here for sure when I finish that.

Anyway, when I resume json 'reading', I may have to again seek help here.

Thanks a lot,
Rajesh

RE: njJson : not succeeding to get it work

(OP)
Hi Marco,

I am back here. Thank you once again for that detailed information with sample codes.

As mentioned earlier, I wrote a program to generate json based on a table. The table defines the parent - child hierarchy, the tables involved and the fields to be used to get and write values. That works fine as far as the current requirement is concerned.

Now, I have to write another program to read from a json (generated using the above mentioned program). I visualise that I can make the same control table as the base and go through each record and retrieve the field/values pairs from the corresponding json branch (as I already have the hierarchy in the control table). I hope it must be possible.

Now, I am wondering, by simply providing the json file and the starting path, if nfJson will be able to retrieve the fields/values pairs into corresponding tables by creating tables/cursors with names same as that of the json member name and field names same as from the json. Is that really a possibility?

Thank you in advance.
Rajesh

RE: njJson : not succeeding to get it work


Hello Rajesh, it's much easier if you provide a sample file and your expected output.

Marco Plaza
@nfoxProject
https://www.github.com/nftools

RE: njJson : not succeeding to get it work

(OP)
Hi Marco,

Thank you very much for offering me your valuable time to help.

A sample json with data is attached.

Basically, I want to get all those <field> : <value> pairs in a cursor whose name will be their parent member name.

For example,

"authPrsn": {

"sbmtrTyp": "ASA",
"sbmtrCd": "ADSPD9028J",
"authReprsntvCd": "BXQPD10234",
"shpngLineCd": "AAFFH9326F",
"authSeaCarrierCd": "AAHFB9023F",
"trmnlOprtrCd": "INCCU1KKP1"
}

I want to get the above data in a cursor called "authPrsn".

Here, I have data in array form also. If you go to member "mastrCnsgmtDec", its an array. Under that there are data from many tables. However, here we have cases that there is a master table, for each record of which, the corresponding records from other tables are appearing below.

For example, if you examine the member "mastrCnsgmtDec", the table "MCRef" is the loop controlling table. For each of its record corresponding records from "prevRef", "locCstm", "trnshpr" etc are printed.
ie, "MCRef" table "lineNo": 1 is printed with records corresponding to "lineNo": 1 in other tables. Then, "MCRef" table "lineNo": 2 is printed and so on. Under this hierarchy there can be another nested loop the same way.

So, how can nfJson help me to retrieve data from this json into corresponding tables/cursors ?
Yes, of course, I am not expecting a magic like I issue a single command and it gives me a set of tables with perfect data.
However, I am wondering what would be the most appropriate & systematic method using nfJson to achieve this.

Would be great if you (or anyone else here friends) could suggest/advise me.

Thanks a lot once again for offering me your help.

Rajesh

RE: njJson : not succeeding to get it work

(OP)
Hi Marco,

I am back here again!
But, it seems you have not been around since few days!

Your suggestion of putting the array element number in brackets is working. Thanks a lot!
It gives me the data in a cursor.

Now I am writing a program to traverse through the complete json and to retrieve data into cursors.

A few issues I observed:

1. Suppose, there are 4 fields in my schema for a particular table.
Now, in my json, if value of a field is blank, it may not print the field and value pair at all. But the next record of the array may have value in the same field and it will be printed. In this scenario, when I run '=nfOpenJson( lcJson, '$.array', lcSchema)', it gives me <the member name> not found error. Do you have any suggestion to overcome this problem?

2. I have to figure out a way of finding the size of an array present in json, ie the number of record present inside an array object in the json.
I am working on that at present.

Thanks in advance.
Rajesh



RE: njJson : not succeeding to get it work

As JSON is short for JavaScript Object notation, it's not about a table or cursor hierarchy, it's about an object having members (simple properties, arrays, objects, collections). This nature is quite similar to XML if you'd interpret the XML node as the root object. Which means you have to live with the fact JSON is a "nested anything" and you can't always directly turn anything into one or even more tables or cursors in VFP. Exactly the same problem as you have with general XML.


I worked with nfJSON already, too, only using it's nfJSONread.prg, Marco already pointed it out:

CODE

public oo
oo = nfJsonRead(m.lcJson) 

That's mainly all you need to turn any (valid!) JSON into a VFP object. From then on, you can turn this object into other things with VFP code, for example using AMEMBERS and FOR loops. Actually that's all you really need. I guess nfJSON provides some further functionalities, but I don't expect anything to turn a single subobject like "authPrsn" is - just having a few string type members, into a table.

Here's a siumple VFP function deconstructing what nfJSONread constructs, which shows how you get at the name/value pairs:

CODE

Set Procedure To nfjsonread.prg

Local lcJSON, loJSON

Clear 

Text To lcJSON NoShow
"authPrsn": {

"sbmtrTyp": "ASA",
"sbmtrCd": "ADSPD9028J",
"authReprsntvCd": "BXQPD10234",
"shpngLineCd": "AAFFH9326F",
"authSeaCarrierCd": "AAHFB9023F",
"trmnlOprtrCd": "INCCU1KKP1"
}
EndText

loJSON = nfjsonread("{"+lcJSON+"}")
ShowStructure(loJSON)

Procedure ShowStructure()
   Lparameters toObject, tnLevel 
   tnLevel = Evl(tnLevel, 0)
   
   Local lnI, lvMember, laMembers[1]
   For lnI=1 To AMembers(laMembers,toObject)
       lvMember = GetPem(toObject,laMembers[lnI])
       If VarType(lvMember)="O"
          ? Space(tnLevel*2)+laMembers[lnI]+" is an object with this structure:"
          ShowStructure(lvMember, tnLevel+1)
       Else 
          ? Space(tnLevel*2)+laMembers[lnI]+" is "+Transform(lvMember)
       EndIf 
   EndFor
EndProc 

Now when you already know this subobject is called authPrsn you can start with loJSON.authPrsn instead of loJSON and you can use AMEMBERS and GETPEM as shown to inert these pairs of values into a cursor instead of just displaying them.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: njJson : not succeeding to get it work

Note: When you'd really have JSON nested in multiple levels this code would fail as laMembers is not scoped as LOCAL variable. In this case it doesn't matter, though. add laMembers[1] to the definition of LOCAL variables and it'll work moire generally.

Bye, Olaf.


Olaf Doschke Software Engineering
https://www.doschke.name

RE: njJson : not succeeding to get it work

The major other way to turn the simple leaf node objects a JSON objhect has into records is via INSERT FROM NAME, VFPs capability to interpret a single object as a record with tits members being the field names. This requires a cursor of that structure precreated to the insert, though:

CODE

Set Procedure To nfjsonread.prg

Local lcJSON, loJSON

Clear

TEXT To lcJSON NoShow
"authPrsn": {

"sbmtrTyp": "ASA",
"sbmtrCd": "ADSPD9028J",
"authReprsntvCd": "BXQPD10234",
"shpngLineCd": "AAFFH9326F",
"authSeaCarrierCd": "AAHFB9023F",
"trmnlOprtrCd": "INCCU1KKP1"
}
ENDTEXT

loJSON = nfjsonread("{"+lcJSON+"}")

Create Cursor authPrsn (sbmtrTyp Varchar(20);
   , sbmtrCd Varchar(20);
   , authReprsntvCd Varchar(20);
   , shpngLineCd Varchar(20);
   , authSeaCarrierCd Varchar(20);
   , trmnlOprtrCd Varchar(20);
   )

Insert Into authPrsn From Name loJSON.authPrsn
Browse 

JS Arrays are a little more complex as Records (of course in themselves they are simpler structures than objects, but JS arrays and VFP arrays differ vastly).

VFP has commands like APPEND FROM ARRAY, that's even older than the variant to insert objects, but require a 2d array, JS doesn't have that. Instead, it has 1d arrays with simple numeric subscripts starting at 0 and arrays with named members. Those arrays could be transformed into objects by nfJSON anyway, so you don't encounter them. But simple arrays like [1,2,3] will need their own handling to be turned into a single column cursor, for example.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: njJson : not succeeding to get it work

(OP)
Hi Olaf,

Thank you so much for your message and sample code.

In fact, our json is data from tables and that was the reason I referred to tables, records etc.

With the 'nfOpenJson( lcJson, '$.array', lcSchema)' command, where lcJson is my json text, '$.array' denotes to start from the root itself and lcSchema is the structure schema. This works fine and it automatically creates a cursor and I am getting the json content in that.

Now, I think, I will try your suggestion of using 'nfjsonread' and retrieving data onto object and then I can parse from it. Too, I already have corresponding tables in place.

Thank you very much
Rajesh


RE: njJson : not succeeding to get it work


Hello Rajesh, sorry for my late reply.

nfOpenJson was left in "beta", so I need to do some tweaks, and use it in conjuntion with nfJsonRead()
to solve this scenario.

I'll get back in a few hous with a full sample.







Marco Plaza
@nfoxProject
https://www.github.com/nftools

RE: njJson : not succeeding to get it work

Quote (Rajesh Karunakaran)

In fact, our json is data from tables

Do you mean a table authPrsn with records like the following exists and was turned into the JSON notation you gave:

Name              Value
sbmtrTyp          ASA
sbmtrCd           ADSPD9028J
authReprsntvCd    BXQPD10234
shpngLineCd       AAFFH9326F
authSeaCarrierCd  AAHFB9023F
trmnlOprtrCd      INCCU1KKP1 

Because when you have such a table and let nfSON create JSON from it, it'll look like this instead:

{
      "arrayofvalues":false,
      "recordcount":6,
      "rows":[
          {
              "name":"sbmtrTyp",
              "value":"ASA"
          },
          {
              "name":"sbmtrCd",
              "value":"ADSPD9028J"
          },
          {
              "name":"authReprsntvCd",
              "value":"BXQPD10234"
          },
          {
              "name":"shpngLineCd",
              "value":"AAFFH9326F"
          },
          {
              "name":"authSeaCarrierCd",
              "value":"AAHFB9023F"
          },
          {
              "name":"trmnlOprtrCd",
              "value":"INCCU1KKP1"
          }
      ]
  } 

Made by this code:

CODE

Local lcJSON

Create Cursor authPrsn (name Varchar(20), value varchar(20))
Insert Into authPrsn Values ("sbmtrTyp", "ASA")
Insert Into authPrsn Values ("sbmtrCd", "ADSPD9028J")
Insert Into authPrsn Values ("authReprsntvCd", "BXQPD10234")
Insert Into authPrsn Values ("shpngLineCd", "AAFFH9326F")
Insert Into authPrsn Values ("authSeaCarrierCd", "AAHFB9023F")
Insert Into authPrsn Values ("trmnlOprtrCd", "INCCU1KKP1")

lcJSON = nfCursorToJson(.f.,.f.,.f.,.t.)
Clear
? lcJSON 

OK, Marco already seems to be working on something you can then use, but in general a table in JSON would need a collection of rows with the repeating record structure.
Just notice that this differs both from your JSON and also from a JSON array the fOpenJson expects to find at the specified position.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: njJson : not succeeding to get it work


PS: I see Olaf already gave you a guide on how to treat this case;
I posted a similar sample with XML and nfXmlRead():

https://groups.google.com/d/msg/publicesvfoxpro/5_...

But your structure can benefit from nfOpenJson capabilities for flattening objects
and saving the arrays as JSON; this way you'll be able to scan the table and use
nfJsonRead() to solve the nested tables.

I'll make the solution for your sample data so others can refer to this thread on vfpx.










Marco Plaza
@nfoxProject
https://www.github.com/nftools

RE: njJson : not succeeding to get it work

(OP)
Olaf,

Quote (Olaf)

Do you mean a table authPrsn with records like the following exists and was turned into the JSON notation you gave:

No, for json writing (the file I uploaded), I wrote my own program. Because my client wanted to control the content for json dynamically. So, we created a master table with participating tables for json content in a Parent-Child combination along with specification like, if a section needs to print as an array, if a section has a looping control table, if a section a master object with its own children, the tag order to be based on while retrieving the data, the relating key combination between master and its children etc etc.

As of now, it is working fine and it produces a json similar to the one I uploaded.

But, for reading I will follow your suggestion. That should work I believe. I just started writing a program with that concept as the base.

Thank you so much,
Rajesh

RE: njJson : not succeeding to get it work

(OP)
Marco,

Thank you for your offers dear!
I really respect the dedication on whatever you are doing!

Now, I am just trying to write a program with Olaf's suggestion as a base.
Will get back to you all when I finish.

Thanks dear team!
Rajesh

RE: njJson : not succeeding to get it work

OK, best wishes for your progress.

If you show how you create the JSON, this might get a good idea to get back to the original data. You might have used TEXT..ENDTEXT (Textmerge). It's not always straight forward to get back data as it was.

I could only recommend in general, that under that circumstance you only need the JSON to exchange data coming from VFP and retrieved back in VFP, when there is nothing else involved needing specific JSON, I'd rather use CURSORTOXML/XMLTOCURSOR. That has a dependency to MSXML3 and 4, but it's workable and has much less headaches in the reversal of generating and parsing. Besides being able to exchange DBF files of free DBFs.


It's a fine idea to keep the JSON shorter, concentrating the information into a single bracket, for example, but as you see the natural way to read this in does not generate a collection of 4 objects or an array with 4 rows, but a single object with 4 properties.

I can imagine you used a SCAN-LOOP to read out name/value and put that into the rows of the single JSON notation. If you read that back, you therefore also need a loop to read out the four properties and split them up and insert them into four records. And that's less straight forward, as you need the list of members for the reverse way. You sacrificed the simple reverse transformation for shorter JSON notation.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: njJson : not succeeding to get it work

Hello Rajesh, after inspecting in more detail you object ( let's forget about it is serialized as json ) the structure shows -as you already said- that it was not generated 'as is' from a relational db ( that's ok ) so trying to turn it into related tables defeats the intention to work with a model that it's thinked to closely represent a entity. Json came into the scene in MySql, Postgres, Sqlserver etc. precisely to bring into the RDBMS world that "noSql" capacities, so the desired way to work with this object is 'as is' by just parsing it with nfJsonRead.

If we need to turn it into parent-child cursors, or even completely flatten the structure, we should:

1) create the related tables ( target tables ) with the columns matching the names of the source objects
2) append blank record on each taget table and proceeed to apply successive "gather name" over it with
source object properties until it has collected all the desired ones ( that's a cool feature of vfp that allows to make an object patch using a table record instead of an object! )
3) iterate arrays, using the same steps above.

Here is the basic technique ( of course I'll leave to you the more complex nested tables winky smile )

CODE -->

*------------------------------------------------
* using nfJsonRead to flatten object structures
*------------------------------------------------

Close Tables All

Public ojson

ojson =  nfjsonread( getjson() )


*-- this cursor will get properties from multiple source objects - 2 properties from each taken as sample: 
Create Cursor flattenSample  ( ;
  senderid c(10),receiverid c(10),;
  msgtyp   c(10),prtofrptng c(10),;
  sbmtrtyp c(10),sbmtrcd    c(10),;
  modeoftrnsprt c(10),grosstonnage N(10,2),;
  voyageno c(10) ,totalnooftrnsprteqmtmnfsted i )


Create Cursor shipitnry  (;
  shipitnryseq c(10),;
  prtofcallcdd c(10),;
  prtofcallname c(10) )

Select flattenSample

Append Blank
Gather Name ojson.headerfield
Gather Name ojson.Master.decref
Gather Name ojson.Master.authprsn
Gather Name ojson.Master.vesseldtls
Gather Name ojson.Master.voyagedtls
edit normal


* observe that only the last entry has all the shipitnry properties
* this is how "object patch" works in vfp using cursor rows

Select shipitnry

For Each oshipitnry In   ojson.Master.voyagedtls.shipitnry
  Append Blank
  Gather Name oshipitnry
Endfor
browse normal



*--------------------------------------
Function getjson()
*--------------------------------------

TEXT TO cjson NOSHOW TEXTMERGE

{
  "HeaderField": {
    "senderID": "FARSHIPPING",
    "receiverID": "INCCU1",
    "versionNo": "1.1.0.2",
    "indicator": "T",
    "messageID": "SACHM23",
    "sequenceOrControlNumber": "1234",
    "date": "20190902",
    "time": "18:59:39",
    "reportingEvent": "SAM"
  },
  "master": {
    "decRef": {
      "msgTyp": "F",
      "prtofRptng": "INCCU1",
      "jobNo": 1234,
      "jobDt": "20190902",
      "mnfstNoRotnNo": 0,
      "vesselTypMvmt": "C",
      "dptrMnfstNo": 0
    },
    "authPrsn": {
      "sbmtrTyp": "ASA",
      "sbmtrCd": "ADSPD9028J",
      "authReprsntvCd": "BXQPD10234",
      "shpngLineCd": "AAFFH9326F",
      "authSeaCarrierCd": "AAHFB9023F",
      "trmnlOprtrCd": "INCCU1KKP1"
    },
    "vesselDtls": {
      "modeOfTrnsprt": "1",
      "grossTonnage": 0.000,
      "netTonnage": 0
    },
    "voyageDtls": {
      "voyageNo": "001",
      "totalNoOfTrnsprtEqmtMnfsted": 4,
      "briefCrgoDesc": "GENERAL CARGO",
      "totalNmbrOfLines": 15,
      "exptdDtAndTimeOfArvl": "20190829T9T12:51",
      "nmbrOfPsngrsMnfsted": 0,
      "nmbrOfCrewMnfsted": 0,
      "shipItnry": [{
        "shipItnrySeq": "-3"
      },
      {
        "shipItnrySeq": "-2"
      },
      {
        "shipItnrySeq": "-1",
        "prtOfCallCdd": "LKCMB"
      },
      {
        "shipItnrySeq": "0",
        "prtOfCallCdd": "INCCU1",
        "prtOfCallName": "KOLKAT"
      }]
    },
    "mastrCnsgmtDec": [{
      "MCRef": {
        "lineNo": 1,
        "mstrBlNo": "SCJUA37AA03388",
        "mstrBlDt": "20190704",
        "consolidatedIndctr": "S",
        "prevDec": "N"
      },
      "prevRef": {
      },
      "locCstm": {
        "firstPrtOfEntry": "DEHAM",
        "destPrt": "INCCU1PLP1",
        "nxtPrtOfUnlading": "INCCU1",
        "typOfCrgo": "IM",
        "itemTyp": "OT",
        "crgoMvmt": "LC",
        "natrOfCrgo": "C",
        "splitIndctr": "N",
        "nmbrOfPkgs": 17.000000,
        "typOfPackage": "PKG"
      },
      "trnshpr": {
        "trnshprCd": "AAA25896Z0",
        "trnshprBond": "2001009852"
      },
      "trnsprtDoc": {
        "prtOfAcptCdd": "DEHAM",
        "prtOfReceiptCdd": "INCCU1",
        "cnsgnesName": "INDO TEEN CO",
        "cnsgneStreetAddress": "9 MITRA LANEKOLKATA-700007 INDIA PH.9831000786",
        "goodsDescAsPerBl": "4X20'GP S.T.C.DEFECTIVE TIN FREE STEEL COILS HS CODE:72105000   NET WEIGHT:101.523 KGS"
      },
      "trnsprtDocMsr": {
        "nmbrOfPkgs": 17,
        "typsOfPkgs": "PKG",
        "marksNoOnPkgs": "N/M",
        "grossWeight": 103223.000,
        "netWeight": 0.000,
        "grossVolume": 0.000,
        "invoiceValueOfCnsgmt": 0.00
      },
      "itemDtls": [{
        "crgoItemSeqNmbr": 1,
        "hsCd": "0507 90",
        "unoCd": "3077",
        "imdgCd": "3",
        "nmbrOfPkgs": 17,
        "typOfPkgs": "PKG"
      },
      {
        "crgoItemSeqNmbr": 2,
        "hsCd": "0507 [Ex",
        "unoCd": "3077",
        "imdgCd": "3",
        "nmbrOfPkgs": 10,
        "typOfPkgs": "PKG"
      }],
      "trnsprtEqmt": [{
        "eqmtSeqNo": 1,
        "eqmtId": "GLDU5395861",
        "eqmtTyp": "CN",
        "eqmtSize": "2200",
        "eqmtLoadStatus": "FCL",
        "eqmtSealNmbr": "EU16898250",
        "cntrAgntCd": "ADSPD9028J",
        "cntrWeight": 0.00,
        "totalNmbrOfPkgs": 5
      },
      {
        "eqmtSeqNo": 2,
        "eqmtId": "TCKU1362860",
        "eqmtTyp": "CN",
        "eqmtSize": "2200",
        "eqmtLoadStatus": "FCL",
        "eqmtSealNmbr": "EU16898247",
        "cntrAgntCd": "ADSPD9028J",
        "cntrWeight": 0.00,
        "totalNmbrOfPkgs": 4
      },
      {
        "eqmtSeqNo": 3,
        "eqmtId": "TCLU7234919",
        "eqmtTyp": "CN",
        "eqmtSize": "2200",
        "eqmtLoadStatus": "FCL",
        "eqmtSealNmbr": "EU16898245",
        "cntrAgntCd": "ADSPD9028J",
        "cntrWeight": 0.00,
        "totalNmbrOfPkgs": 4
      },
      {
        "eqmtSeqNo": 4,
        "eqmtId": "TRHU1592710",
        "eqmtTyp": "CN",
        "eqmtSize": "2200",
        "eqmtLoadStatus": "FCL",
        "eqmtSealNmbr": "EU16898246",
        "cntrAgntCd": "ADSPD9028J",
        "cntrWeight": 0.00,
        "totalNmbrOfPkgs": 4
      }],
      "itnry": [{
        "prtOfCallSeqNmbr": 1,
        "prtOfCallCdd": "DEHAM",
        "prtOfCallName": "HAMBURG",
        "nxtPrtOfCallCdd": "INCCU",
        "nxtPrtOfCallName": "KOLKATA"
      },
      {
        "prtOfCallSeqNmbr": 2,
        "prtOfCallCdd": "INCCU",
        "prtOfCallName": "KOLKATA",
        "nxtPrtOfCallCdd": "INCCU"
      }],
      "suplmntryDec": {
      },
      "houseCargoDec": [{
        "crgoDecRef": {
          "lineNo": 1,
          "mstrBlNo": "HBL001",
          "mstrBlDt": "20190704",
          "consolidatedIndctr": "S",
          "prevDec": "N"
        },
        "trnsprtDoc": [{
        }],
        "itemDtls": [{
          "eqmtSeqNo": 1,
          "eqmtId": "CNTRNO-1-1",
          "cntrWeight": 0.00,
          "totalNmbrOfPkgs": 0
        }],
        "trnsprtEqmt": [{
        }]
      },
      {
        "crgoDecRef": {
          "lineNo": 2,
          "mstrBlNo": "HBL002",
          "mstrBlDt": "20190704",
          "consolidatedIndctr": "S",
          "prevDec": "N"
        },
        "trnsprtDoc": [{
          "prtOfAcptCdd": "LKCMB",
          "cnsgnrsName": "ABCD",
          "cnsgnrStreetAddress": "COLOMBO",
          "cnsgnesName": "XXXXXCONSIGNEE"
        },
        {
          "prtOfAcptCdd": "SGSIN",
          "cnsgnrsName": "SINGAPORE CONSIGNOR",
          "cnsgnesName": "KOLKATA COSIGNEE"
        }],
        "itemDtls": [{
          "eqmtSeqNo": 2,
          "eqmtId": "CNTRNO-1-2",
          "cntrWeight": 0.00,
          "totalNmbrOfPkgs": 0
        }],
        "trnsprtEqmt": [{
        }]
      }]
    },
    {
      "MCRef": {
        "lineNo": 2,
        "mstrBlNo": "SCJUA37AA03391",
        "mstrBlDt": "20190704",
        "consolidatedIndctr": "S",
        "prevDec": "N"
      },
      "prevRef": {
      },
      "locCstm": {
        "firstPrtOfEntry": "DEHAM",
        "destPrt": "INCCU1CPL2",
        "nxtPrtOfUnlading": "INCCU1",
        "typOfCrgo": "IM",
        "itemTyp": "OT",
        "crgoMvmt": "LC",
        "natrOfCrgo": "C",
        "splitIndctr": "N",
        "nmbrOfPkgs": 10.000000,
        "typOfPackage": "PKG"
      },
      "trnshpr": {
      },
      "trnsprtDoc": {
        "prtOfAcptCdd": "DEHAM",
        "prtOfReceiptCdd": "INCCU1",
        "cnsgnesName": "INDO TEEN CO",
        "cnsgneStreetAddress": "9 MITRA LANE KOLKATA,700007INDIA PH.9831000786",
        "cnsgneCity": "KOLKATA",
        "cnsgneCntrySubDivName": "WEST BENGA",
        "cnsgneCntrySubDiv": "10",
        "cnsgnePstcd": "700007",
        "goodsDescAsPerBl": "2X20'GP S.T.C  DEFECTIVE TIN FREE STEEL COILS  HS CODE:72105000  NET WEIGHT:49692 KILOS"
      },
      "trnsprtDocMsr": {
        "nmbrOfPkgs": 10,
        "typsOfPkgs": "PKG",
        "marksNoOnPkgs": "N/M",
        "grossWeight": 50512.000,
        "netWeight": 0.000,
        "grossVolume": 0.000,
        "invoiceValueOfCnsgmt": 0.00
      },
      "itemDtls": [{
        "crgoItemSeqNmbr": 1,
        "hsCd": "07, 09 o",
        "nmbrOfPkgs": 10,
        "typOfPkgs": "PKG"
      },
      {
        "crgoItemSeqNmbr": 2,
        "hsCd": "0802, 08",
        "nmbrOfPkgs": 10,
        "typOfPkgs": "PKG"
      },
      {
        "crgoItemSeqNmbr": 3,
        "hsCd": "10",
        "nmbrOfPkgs": 10,
        "typOfPkgs": "PKG"
      }],
      "trnsprtEqmt": [{
        "eqmtSeqNo": 0,
        "eqmtId": "TCKU3438645",
        "eqmtTyp": "CN",
        "eqmtSize": "2200",
        "eqmtLoadStatus": "FCL",
        "eqmtSealNmbr": "EU16899299",
        "cntrAgntCd": "ADSPD9028J",
        "cntrWeight": 0.00,
        "totalNmbrOfPkgs": 5
      },
      {
        "eqmtSeqNo": 0,
        "eqmtId": "TLLU8090373",
        "eqmtTyp": "CN",
        "eqmtSize": "2200",
        "eqmtLoadStatus": "FCL",
        "eqmtSealNmbr": "EU16899293",
        "cntrAgntCd": "ADSPD9028J",
        "cntrWeight": 0.00,
        "totalNmbrOfPkgs": 5
      }],
      "itnry": [{
        "prtOfCallSeqNmbr": 1,
        "prtOfCallCdd": "DEHAM",
        "prtOfCallName": "HAMBURG",
        "nxtPrtOfCallCdd": "INCCU",
        "nxtPrtOfCallName": "KOLKATA"
      },
      {
        "prtOfCallSeqNmbr": 2,
        "prtOfCallCdd": "INCCU",
        "prtOfCallName": "KOLKATA",
        "nxtPrtOfCallCdd": "INCCU"
      }],
      "suplmntryDec": {
      },
      "houseCargoDec": [{
        "crgoDecRef": {
          "lineNo": 1,
          "mstrBlNo": "LINE-2-NO-1",
          "mstrBlDt": "20190729",
          "consolidatedIndctr": "S",
          "prevDec": "N"
        },
        "trnsprtDoc": [{
        }],
        "itemDtls": [{
          "eqmtSeqNo": 1,
          "eqmtId": "CNTRNO-2-1",
          "cntrWeight": 0.00,
          "totalNmbrOfPkgs": 0
        }],
        "trnsprtEqmt": [{
        }]
      },
      {
        "crgoDecRef": {
          "lineNo": 2,
          "mstrBlNo": "LINE-2-NO-2",
          "mstrBlDt": "20190730",
          "consolidatedIndctr": "S",
          "prevDec": "N"
        },
        "trnsprtDoc": [{
        }],
        "itemDtls": [{
          "eqmtSeqNo": 2,
          "eqmtId": "CNTRNO-2-2",
          "cntrWeight": 0.00,
          "totalNmbrOfPkgs": 0
        }],
        "trnsprtEqmt": [{
        }]
      }]
    }],
    "prsnOnBoard": [{
      "prsnOnBoardSeqNmbr": 1,
      "prsnId": {
        "prsnIdDocExpiryDt": "20000808",
        "prsnIdOrTravelDocIss": "US",
        "prsnIdOrTravelDocNmb": "S90006875",
        "prsnIdOrTravelDocTyp": "980"
      }
    },
    {
      "prsnOnBoardSeqNmbr": 2,
      "prsnId": {
        "prsnIdDocExpiryDt": "20010810",
        "prsnIdOrTravelDocIss": "TR",
        "prsnIdOrTravelDocNmb": "T3456875",
        "prsnIdOrTravelDocTyp": "980"
      }
    }],
    "voyageTransportEquipment": {
    },
    "tmSuprtDocs": {
    },
    "tmAdtnlDec": {
    }
  },
  "digSign": {
  }
}

ENDTEXT

Return m.cjson 








Marco Plaza
@nfoxProject
https://www.github.com/nftools

RE: njJson : not succeeding to get it work

Marco said something important, that I second. JSON was introduced to just be a text notation of Javascript memory objects. And everything in JS is an object. JS is no database language like VFP, so it can't really cope with SQL. It can do HTTP requests, and that's the basis of a few NoSQL databases to store JSON notation as "documents" in document collections and make them retrievable via a HTTP interface. It's not the only type of NoSQL and not the only reason for NoSQL databases, but enough of that.

JSON as text can of course also be stored in files, and Marco has written his library specifically for VFP to be able to also consume JSON into objects or cursors.

It's not a wrong idea to use it though you don't really do anything related to JS or the web with HTTP, just because JSON notation is shorter than XML, not as verbose. Also, it is a bit better than CSV, as it can represent something more complicated than a single table.

But Marco reminded me of the way to display DBFs "pivoted", single records appear as rows, if you don't BROWSE a workarea, but EDIT it. That's still actually a single record and not really pivoted data put into a table with two columns. Just note you can also get there if you browse a workarea. While the browse window has focus, pick the menu item "Edit" from the "View" menu, and the display changes.

If that is your original situation my code sample with the Insert Into authPrsn From Name loJSON.authPrsn works, just end it with EDIT instead of BROWSE. I'm not sure, if that was all, since Marcos of course did more: He "flattened" the data of several JS (child) objects that reflect 1:1 that way as VFP (child) objects into a single record. (So no worries, Marcos, I recognize what you did there).

Then you actually don't need much special code, you already at least get all data into the object hierarchy VFP can also have - just like JS, via nfJSONread. Just as I already said. And then you can populate records from there. You even need less of a transformation or iteration of AMEMBERS to get out the single values. INSERT FROM NAME and GATHER NAME are your major tools to put object properties into table fields.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: njJson : not succeeding to get it work


Hi Olaf.. you're right, insert from name suffices if you don't need to flatten the structure.. just wanted to leave this sample to show the nfOpenJson alternative. I suggest deprecate the use of nfOpenJson in favor of this technique,
because I won't continue working with it given the fact that I already developed a soon-to-be-released set of new functions ( nfTools ) that will make this task and many alike easier.















Marco Plaza
@nfoxProject
https://www.github.com/nftools

RE: njJson : not succeeding to get it work

(OP)
Thank you Olaf & Marco,

Thank you so much for your samples and Marco, really excited to see your new tool!

Now, I am half way through writing my code for json read and save to tables. However, I am a bit confused on one thing.
If you refer to the same json file we're discussing with, once I made an object out of it, I can traverse through each element hierarchically (thanks to Olaf's code). If I watch the object in 'Locals' window, I can see that 'ShipItnry' is shown an 'A'. But if I check VARTYPE it is 'O'. Whatever it may be, how can I retrieve the size of the array 'ShipItnry'?


Thanks,
Rajesh

RE: njJson : not succeeding to get it work

Let me see...

CODE

"shipItnry": [{
        "shipItnrySeq": "-3"
      },
      {
        "shipItnrySeq": "-2"
      },
      {
        "shipItnrySeq": "-1",
        "prtOfCallCdd": "LKCMB"
      },
      {
        "shipItnrySeq": "0",
        "prtOfCallCdd": "INCCU1",
        "prtOfCallName": "KOLKAT"
      }] 

From JSON notation perspective this is an array. And the array elements are objects. You get types A from the debugger, as that is an array, you get O from vartype, because when addressing an array name you actually address the first array element. You also get "A" from Type("shipItnry",l), look up the help topic of TYPE(). Vartype does NOT provide that.

And to get the length you have ALEN in Foxpro.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: njJson : not succeeding to get it work

More precisely TYPE("ojson.Master.voyagedtls.shipitnry",1) will be "A" and ALEN(ojson.Master.voyagedtls.shipitnry) tells you the array length.

Marco reads the elements in here with a FOR EACH loop, that means it doesn't matter how many elements there are in the array, this simply iterates each of them:

CODE

Select shipitnry

For Each oshipitnry In   ojson.Master.voyagedtls.shipitnry
  Append Blank
  Gather Name oshipitnry
Endfor
browse normal 

So Marcos code is specific to the structure you gave, it makes use of known names, but it's not depending on knowing the count of elements.

The array elements (objects) read in as records then also have a count as end result, RECCOUNT("shipitnry").

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: njJson : not succeeding to get it work

(OP)
Olaf,

I am getting 'A' when I check through TYPE function with 1 as parameter. However, I was getting 'Not an array' message when tried ALEN on that. That's why I was confused. Now, I got it by using a macro substitution as in

? ALEN(mbr.&arr[2])

where mbr is my object and arr[2] is its 2nd element which is actually an array.

However, I think, I should try and go with that 'For each...' method mentioned by Marco.
I am checking with these options and suggestions.
Will get back here soon.

Rajesh

RE: njJson : not succeeding to get it work

Are you working on yet other JSON? Because I was just using Marcos example, and there isn't an array containing a nested array, especially not shipitnry.

And closely watch, what I did. TYPE needs the name of the array variable (actually here an array property) as a string, ALEN() needs the array itself. Also, the fully qualified name of the array starts with the variable ojson, there is no array variable shipitnry, the array is a property of ojson.Master.voyagedtls, namely ojson.Master.voyagedtls.shipitnry. You can't shorten this name. You have the variable ojson, that has the JSON root object Master, which has a voyagedtls object, of which one property is an array shipitnry.

What nfreadJSON generates is 1:1 as nested as the object would be, if you'd unserialize it (that means load and set a variable to it) in JS.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: njJson : not succeeding to get it work

(OP)
Olaf,

I successfully managed to write a program to retrieve data from the json object into its respective tables. The handling of array areas also appears to be okay now.

However, I have a problem. When nfJson object for a json is created, it rearranges the members in alphabetical order. This actually disturbs the hierarchy flow. For example, in the json I have members 'mcRef' and then 'houseCargoDec'. But when I look at the object, the 'houseCargoDec' comes first and then the 'mcRef'.

In fact, 'mcRef' contains master table data and 'houseCargoDec' contains data of a few child tables. I want to write a few field values of master into the child corresponding fields. But, now because this alphabetic order my child data is read first and I am stuck up.
I think Marco can help me to come out of this situation.

Dear Marco,
Could you just check this and help me out, please ?

Apart from this, things seem to be satisfactory and a BIG SALUTE to your nfJson, dear Marco!



Thanks in advance,
Rajesh

RE: njJson : not succeeding to get it work

Amembers works that way, there and there also is no way around that when you start working on the unserielized object.

To get things in order of the JSON, you'd need to write code like nfJSONread on the parsing level.

Reading the properties, arrays, and child objects of the VFP ojson.Master object, there is no way to list the members in the chronology they were added to the object.

On the one side I see a helpful extension for your case may be a logging of the chronological order in which objects are added to VFPs ojson.Master.


Before I'd suggest MArco to add that, I'd argue it's not the job of such a library. You point out certain records need to come first because of relationships (You can't add detail data before head data). But you have the same problem in a database you'd process in ADBOBJECT() order. And the solution is to simply don't do it.

Just like a database isn't the list of tables in alphabetical order the ojson.Master object isn't the object tree in alphabetical and/or tree branch order, you read the child objects you need first, just like an SQL script adding a complex entity with a data hierarchy in 2 or more tables from head.

If you base your code on my iteration of AMEMBERS(), then why? Just like a specific database will always have a specific head data table, the ojso.Master has one specific child object that interests you first, then you're just as free to read that first like you can write SELECT * FROM headertable, no matter if there are tables starting with A-G before that.

Last5 not least, it would be very unusual, if traversing an object tree from root level to child nodes would confront you with a situation you come across some child/detail data before head data, as the object hierarchy also is only working from parent to child objects, head to details and that's very generally so, no matter what JSON you have. So, that may simply point out you did put data on the same tree depth level, that should perhaps rather be nested.

Because the main processing order of iterating AMEMBERS() is still the hierarchy levels of the object tree before it is about the alphabetical order of the nodes on the same level. If those levels represent the hierarchy level of tables, too, data on the same level should not have a relationship that needs to process a node before it's sibling nodes. The solution to that could actually be in the JSON generation process rather than the parsing / unserialization or the processing of the result VFP object tree.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: njJson : not succeeding to get it work

(OP)
Marco / Olaf,

Please find pics to help with the alphabetical reordering issue in my message above.

Rajesh

RE: njJson : not succeeding to get it work

(OP)
Olaf,

The object, in its creation level itself, was made to be rearranged in alphabetical order it appears. That's why I am looking forward to our Marco's advice/suggestion on this.

What I expect is that when the object is created from the json file, the order in which each member, sub-members etc appear in the json file should not be disturbed. When you explode the object, it should show the tree structure in the same order that is present in the json file.

Marco, could you help? Is there any way to achieve this?

Rajesh

RE: njJson : not succeeding to get it work

You can't have that.

Neither the debugger nor IntelliSense will show you the members of an object in another order than alphabetical, nor does AMEMBER list members in the same sort order they were added.

But what hinders you to address the subobject how you want it? If you want to set a form position by setting top and left values, you just do that, you can change top before left and you don't need to read or set any other properties, do you?

Your code will be the sort order of how you process this object.

In a POS system where you want to iterate through receipts and then positions, you simply do that you nest two scan loops, the outer one iterates receipts and the inner one positions. You write specific code addressing the child nodes in the order you need.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: njJson : not succeeding to get it work

(OP)
Olaf,

I just tried what you said. Just created an object and add properties 'ccc', 'bbb' & 'aaa' and when I accessed it using AMEMBERS, it is 'aaa', 'bbb' & 'ccc'. Yes, Olaf, I am forgetting that plan!

Let me think of an alternative to solve the problem.

I have the order of members that I want in a control table. Maybe, after retrieving the sub members of a member from the object, into an array using 'AMEMBERS', I can probably sort it again according to the order in the table and then can start working on them one by one. This is one possible way, I believe. Let me try that.

Whatever may be the way, I have to succeed!

Thanks
Rajesh

RE: njJson : not succeeding to get it work

(OP)
Dear Marco,

Just to make the things clear! I posted it like your nfJson disturbs the actual order of members it reads during the creation of the object. But, Olaf made it clear that AMEMBERS gives you the member list in alphabetical order only. So, I am really sorry about my statement about your nfJson.

That library is the key for my program and salutes to nfJson once again!

Now, I am trying to figure out a way to solve the problem.
Will get back here once I find something.

Thanks,
Rajesh

RE: njJson : not succeeding to get it work

Quote:

I have the order of members that I want in a control table

That's fine, then you can do that. Something like

CODE -->

...
ojson= nfreadJSON(...)
...
SELECT controltable
SCAN
   lcMember = controltable.objectname
   * do soemthing WITH ojson.master.&lcMember
ENDSCAN 

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: njJson : not succeeding to get it work


Hello Rajesh don't worry. Your question/requirement is a very common one , and Olaf already gave you the reason why it happens and why you don't need to have the members in a particular order since your driving program dictates the order in wich elements are processed.

Important note: we should not confuse the Json representation of the object ( the json document ) with the object loaded in memory. This always arise when we want to compare json documents visually or using diff tools, discovering it is not possible when json is generated by different tools; that's where object schemas come to play, but it will add a penalty in speed object serialization. I don't want to enter in much details, but nfTools will support that option
by inferring the object schema from json.

Finally, you might find useful Microsoft's Rest Api guide ( https://github.com/microsoft/api-guidelines/blob/v... ):

Clients MUST NOT rely on the order in which data appears in JSON service responses. For example, clients SHOULD be resilient to the reordering of fields within a JSON object. When supported by the service, clients MAY request that data be returned in a specific order. For example, services MAY support the use of the $orderBy querystring parameter to specify the order of elements within a JSON array. Services MAY also explicitly specify the ordering of some elements as part of the service contract. For example, a service MAY always return a JSON object's "type" information as the first field in an object to simplify response parsing on the client. Clients MAY rely on ordering behavior explicitly identified by the service.

Marco Plaza
@nfoxProject
https://www.github.com/nftools

RE: njJson : not succeeding to get it work

And perhaps the most prominent case against relying on the order of properties in JSON is how JS itself handlöes this, once you write

let x = { "zzz": 42, "mmm": 21, "aaa": 0 }

in the console of your Browser (press F12 right now) and then use intellisense, the properties also appear sorted and interleaved with standard properties every JS object has (that compares to VFPs custom class already having some methods and properties by default).



And that means, you mainly know the child object names when you'd do the parsing yourself. Marco could offer hoiok methods (something like afterparseobject, afterparsearray) that he calls with the name or object or array itself, that is just added to ojson.master and you could work with that.

I don't know if Marco would be happy with such a code design.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: njJson : not succeeding to get it work

(OP)
Olaf / Marco,

The hierarchy order problem also is solved now!
I am getting the data into their respective tables.

Thank you so much for all your help!
Rajesh

RE: njJson : not succeeding to get it work

(OP)
Olaf / Marco,

In fact, I know the order in which the members appear should not be a dependency. But, I had a peculiar problem.

My client receives json file from someone else. Suppose we have data for 2 tables "mcref" and "cargodtl" in the json. In json file, "mcref" comes first and then the "cargodtl". These are related by the key field "IdNo". These are printed in a hierarchy way in json, ie for each "mcref" record there may be multiple "cargodtl" records. However, we have seen that, in the json, under "cargodtl" data, the "IdNo" field and value are not printed as a field:value pair. So, when I read the data of "cargodtl", obviously my "IdNo" field doesn't get populated. So, I have to update it with the IdNo value I read from "mcref". But then for this, the "mcref" should be read first to get the current "IdNo" and then the "cargodtl". But, as AMEMBERS made members in alphabetical order, my program was coming across the member "cargodtl" first and then there was no way to get the "IdNo" value from "mcref" (which has even not yet been read). That was the issue I am facing.

But, now I have solved it I believe. Checking the data to confirm.

Thanks
Rajesh

RE: njJson : not succeeding to get it work

You could always create a set of cursors that don't have foreign key checks and populate them in any order, but whatever, it's good the problem is finally solved.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: njJson : not succeeding to get it work

(OP)
Olaf,

There are no foreign key checks of any kind. I was just not able to determine the key value from the master table that has to be written to the child one because the child was being read first before the master. Now, before start working on the member list, I am reordering the array of members to match the order in which they are supposed to be. And then, when I read, I get to read the master first and then the child. Then, I know the key value from the master that I have to write to the child table. Thus, the problem solved.

Thank you all for your help, guidelines and time
Rajesh

RE: njJson : not succeeding to get it work

I see: You generate keys. Well, you still can read into a set of cursors with an NULL default column you then set as aftermath. You'll need to have some relationship to know which detail data will need the ID of a parent as foreign key.

In object hierarchies, the parent-child relationship is given by a child pointing to its parent. VFP objects have a parent property for that. So this form of data needs no key, still, you can store IDs in JSON, too. I thought, for example, in your sample JSON about shipItnry the shipItnrySeq could be such an id. Unusual with negative numbers, but why not? If that appears in other objects in the JSON you have such relationships already coming from the JSON, not generated by you.

Anyway, it doesn't matter, whether you generate them or they are in the data itself, by a property or by object hierarchy, you still never depend on a sort order of data as you can always load it into a "queue", your own data structure you need to finally apply it in the order you want.

You solved it with meta control data and that's okay, but it wasn't necessary and your only choice or possibility.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: njJson : not succeeding to get it work

(OP)
Marco,

Just a thought. If the json file is invalid for some reason, the nfJsonRead program generates an error.
Is there any way to trap this? If so, my calling program can handle it appropriately and alert the user about the situation.

Rajesh

RE: njJson : not succeeding to get it work

Yes, with ON ERROR.

Marcos routines throw errors using the ERROR command and that means ERROR() is 1098 and the message is as the text Marco puts together.
The only a bit unfortunate thing about the ERROR command is, the lineno of error reported by LINENO() in your error handler is reported as the one with the ERROR statement.

In some way that's ok, but I had quarrels with developers pointing out the place of error is my routine...

The usual error of a native function that's not VFP code is the line of the caller, as in a clean tested working language you don't have errors in a function but places where you put much thought work in checking errors made by the caller, throwing an error exception to inform him about that.

And the other even bigger disadvantage is, that you don't get the chance to continue code execution after the erroneous call, you can only go back to the line inside the function after the ERROR or (what makes even less sense, let RETRY go back to the ERROR command.

Aside of that bad design of ERROR (just in my opinion - and to be clear that's MS VFP team fault, not Marco's), that's already included, Rajesh, you simply need to handle these errors in your error handling.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: njJson : not succeeding to get it work

Besides, ERROR also works to throw you into the CATCH of a TRY CATCH. You can for example do:

CODE

Try
  oJSON = nfjsonread(cJSON)
Catch to loException as Exception
  ? loException.ErrorNo, loException.Message
Endtry 

So you have all methods of error handling with those errors reported by Marcos nfJSON programs.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: njJson : not succeeding to get it work

(OP)
Olaf,

I know TRY...CATCH. But, for this one, I have never thought in that way.
A TRY CATCH routine should do it decently.

Thanks
Rajesh

RE: njJson : not succeeding to get it work

(OP)
Hi Team!

Thank you so much everyone for your time and fantastic suggestions!
I got a bit late to come back here and say this, though.

Now, my writing and reading programs work the way I wanted it.
Some tweaks are still required but that's not related to nfJson or the core process.

with lot of proud, being here smile
Rajesh

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close