How to create Json from cursor in the format below using nfJsonCreate 2

[Sng1]

Create Cursor Library ( Library c(15), shelf N(6), Version c(10), hash c(10),Author c(30), Name c(20), category c(20),Pages N(4),Publication c(60))

Insert into Library Values("Books",5,"LIB3.1.2","hash","Anderson","Legends","Drama",120,"Happer Collins")
Insert into Library Values("Books",5,"LIB3.1.2","hash","Anderson","Ths Blooms","Fiction",450,"Penguin")
Insert into Library Values("Books",5,"LIB3.1.2","hash","Sofie Jia","RRR","Fiction",120,"Blue Dart")
Insert into Library Values("Books",5,"LIB3.1.2","hash","Sofie Jia","Heavy Hearts","Documentary",100,"Mcgrath")

I want Json in below format :-

{
      "details":[
          {
              "author":{
                  "books":[
                      {
                          "bookdtl":{
                              "category":"Drama",
                              "pages":120,
                              "publication":"Happer Collins"
                          },
                          "name":"Legends"
                      },
                      {
                          "bookdtl":{
                              "category":"Fiction",
                              "pages":450,
                              "publication":"Penguin"
                          },
                          "name":"Ths Blooms"
                      }
                  ],
                  "name":"Anderson"
              }
          },
          {
              "author":{
                  "books":[
                      {
                          "bookdtl":{
                              "category":"Fiction",
                              "pages":120,
                              "publication":"Blue Dart"
                          },
                          "name":"RRR"
                      },
                      {
                          "bookdtl":{
                              "category":"Documentary",
                              "pages":100,
                              "publication":"Mcgrath"
                          },
                          "name":"Heavy Hearts"
                      }
                  ],
                  "name":"Sofie Jia"
              }
          }
      ],
      "hash":"hash",
      "library":"Books",
      "shelf":5,
      "version":"LIB3.1.2"
  }

 

[Mike Lewis]

I don't have any personal experience with this, but I know that Rick Strahl has published some JSON tools for VFP. A good place to start would be

https://webconnection.west-wind.com/docs/_1wu19n5bb.htm.

And there is more information here:

https://webconnection.west-wind.com/docs/_1wu18owba.htm

And here is some sample code that I picked up from the first of the above pages:

SELECT * FROM customers ;
   INTO CURSOR TCompanies ;
   ORDER BY company

loSerializer = CREATEOBJECT("wwJsonSerializer")
loSerializer.FormattedOutput = .T.
loSerializer.PropertyNameOverrides = "firstName,lastName,shipAddr,billRate"

*** Notice the 'cursor:alias' syntax
lcJSON = loSerializer.Serialize("cursor:TCompanies")

Of course, this assumes that you have the wwJSONSerializer class available.

Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Do you mean nfJsonCreate from

https://github.com/VFPX/nfJson,

right?

How about using it s it's explained?

cJsonString = nfJsonCreate(oVfp, lFormattedOutput, lNoNullArrayItems,cRootName,aMembersFlag)

lFormattedOutput is the parameter you need to set .T. to get formatted output.

Besides, since you have a cursor and want that to be turned into a JSON string, there is a function that suits it better:

cJsonString = nfCursorToJson(lReturnArray, lArrayofValues, lIncludestruct, lFormattedOutput) converts current open table/cursor to Json

Again, it has a parameter lFormattedOutput for formatted output.

Chriss

 

[Chris Miller]

Without looking into all details I bet you don't get exactly that output, no matter if formatted or not as the JSON you show nests data and all you have in the cursor is a flat list.

So what's missing to get to your goal is a data structure that reflects what the JSON is. On the simplest level, there is nothing in your cursor that is "details" by name or by value.

Chriss

 

[Sng1]

Do you mean nfJsonCreate from

https://github.com/VFPX/nfJson,

right?

Yes, I means nfJson.


I know about nfCursorToJson but it doesn't gives Json in the format I require as I want to create Json in the exact format as I have quoted above as it is statutory report and Govt. is asking for json in the above format. Maybe if it possible to use combination of nfJsonCreate and nfCursorToJson to get the required output. But @mplaza could reply it better as he is inventor of this utility.

Hi Mike,
I personally feel nfJson easy to use tool and could be used as per requirement but will give it a try. For converting json to cursor and support from Marco , nfJson is working wonderful for my project. For converting cursor to JSON , I need json in specific object.

 

[Mike Lewis]

Yes, I hadn't taken in that you specified nfJasonCreate in the subject line of the thread. But, in any case, as I said, I don't have any experience of this, so don't put too much weight on what I say.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

I want to create Json in the exact format as I have quoted above as it is statutory report and Govt.

Well, just look at the one spot I already talked about, the JSON starts (aside of some brackets with "details". No routine in nfJSON takes a template and puts in your data, they all turn the data and names of fields into JSON, so you're at least missing something that is named details.

If you turn it around and turn the JSON into what it stands for, an OBJECT (it's [highlight #FCE94F]J[/highlight]ava[highlight #FCE94F]S[/highlight]cript [highlight ]O[/highlight]BJECT [highlight #FCE94F]N[/highlight]otation). So you can't expect the library to take in your data and create the json from it. The simplest thing to do is to create an object with properties and subobjects that turn to the nested json you want.

So, first step is take the JSON and turn it to an object, not to a cursor. Or even simpler, start a browser, start developer tools, the JS console and paste in this JSON srting in and ENTER, and get an object tree displayed:

Looks quite like the JSON looks, doesn't it? The reason is JSON notation is really just about representing a JS object.

You see, like in the JSON string the four values "hash","library","shelf", and "version" are only properties of the root object. They exist there as one set of properties, in your data that is existing in all 4 records. At this point you must think you have a cleaner form of the data, as you have just a flat list and this browser JS console representation of a JS object looks even more complicated than the JSON string. Well, think again. This structure has two nesting levels and that's pointing out to represent this in the purest form of data you at least need three tables and relations between them. A libraries table with one record, an authors table and a books table, 1:n:m related. And then you can turn that into an object and that can be turned into such a JSON string.

Chriss

 

[Sng1]

As far as structure and relationship in data is concerned , Govt. is also supplying schema for the json , so knowing structure is not an issue. For specific requirement, I have hardcoded to prepare JSON. I want to know if any utility is available which can prepare json from data and meta data. nfjsonread is useful for converting json to dbf but I am not able to use vice-versa. Chris as you told to create json from object. Please correct if I am wrong - First creating object and then converting to json is equivalent to directly making json as I have hardcoded to get the required result. Or something else you are telling .

 

[Chris Miller]

In very short, as JSON is a notation for objects, the only way to have forward and backward conversions is from JSON to object and back.

It is easier to create an object from your data and turn that into a JSON string than to create the JSON string itself. Just look into SCATTER TO NAME [ADDITIVE]. You can easily create an object andd let it have subobjects and then have the nfJsonCreate do the job. But notice, its first parameter oVFP is an object, not a cursor.

knowing structure is not an issue

The table structure and relationships do matter, you need to have the single details, not the joined data, to put together the oVFP object via SCATTER, not join the data into a single flat list.

What you do is like saying here, i have a kitchen (nfJSON) and ingredients. And now the kitchen should cook a meal. You still have to do a few things yourself, know a recipe to get to the goal you want.

Chriss

 

[mplaza]

Hello sng, as Chris already stated, you need to create the object, then just "stringify" it using nfJsonCreate:

set path to "\vfpx\nfxml\nfxml;\vfpx\nfjson\nfjson"

close tables all
clear
create cursor library ( library c(15), shelf n(6), version c(10), hash c(10),author c(30), name c(20), category c(20),pages n(4),publication c(60))

insert into library values("Books",5,"LIB3.1.2","hash","Anderson","Legends","Drama",120,"Happer Collins")
insert into library values("Books",5,"LIB3.1.2","hash","Anderson","Ths Blooms","Fiction",450,"Penguin")
insert into library values("Books",5,"LIB3.1.2","hash","Sofie Jia","RRR","Fiction",120,"Blue Dart")
insert into library values("Books",5,"LIB3.1.2","hash","Sofie Jia","Heavy Hearts","Documentary",100,"Mcgrath")


scatter fields hash,library,shelf,version name root

select distinct author from library into cursor cauthors

addproperty(root,textmerge('details(<<_tally>>)'))

scan

	oDetail = createobject('empty')
	
	addproperty(oDetail,'name',author)

	select * from library where author = oDetail.name into cursor books

	addproperty(oDetail,textmerge('books(<<_tally>>)'))

	scan

		scatter fields name name book
		scatter fields category,pages,publication name bookdtl
		addproperty(book,'bookdtl',m.bookdtl)
		oDetail.books(recno()) = m.book

	endscan
        
        oAuthor = createobject('empty') && edit / added missing node!
	addproperty(oAuthor,'author',m.oDetail) 

	m.root.details(recno('cauthors')) = m.oDetail

endscan

strtofile( nfjsoncreate(m.root,.t.),'tek-tips.json')
modify file tek-tips.json nowait

Marco Plaza
@nfoxProject

https://www.github.com/nftools

 

[Sng1]

Thank You Chris for your valuable advice. Will remember it for various other transformations also like json,xml etc.

Thank You Marco, it is exactly what I needed. Again very grateful from the bottom of the heart for such a wonderful yet simple product.

 

[Chris Miller]

Sng, if you look into it exactly, the JSON created by Marco Plazas code lacks the layer of having an author object stored into each detail array element.

I'd also point out that it shows how the design is not straight forward. And the json you have there, Sng1, is not a template, that's just an example json object and indeed it would be much better to have the general template idea behind this json.

Like always in general, one XML file or json string can be multiple trees of hierarchical data, and this is one tree of hierarchical data in a mixture of arrays (of objects) and objects. A list of data only is one special case of a simple tree without a nested hierarchy.

Chriss

 

[Sng1]

the JSON created by Marco Plazas code lacks the layer of having an author object stored into each detail array element.

Good observation Chris. I got the basic idea to create the complex Json .

it would be much better to have the general template idea behind this json

I have template available. What is expected after that ? Should I write a general procedure or create separate procedures for each json ?

Like always in general, one XML file or json string can be multiple trees of hierarchical data, and this is one tree of hierarchical data in a mixture of arrays (of objects) and objects.

Yes , I have few conversion having multiple trees and each tree with different structure having nested arrays and objects.
Now I am creating generalize procedure which can scan the template to create json from data. Am I going in right direction ?

 

[Chris Miller]

The missing "author" nodes can be done by letting each detail array alement be an object that has just one property "author" which is the author object that Marco put into the detail array. And needing an object that is nothing but one property which in turn is the actual object, well, that's a bad design decision.

Instead they could have called the details array authors array and everything would be much clearer and cleaner.

Here's code that generates the JSON from hierarchical data that it actually represents:

Set Path To C:\whereever\you\have\nfJson ADDITIVE && <-- adapt this

Close Tables All
Clear
Create Cursor libraries (Id i, Library C(15), shelf N(6), Version C(10), hash C(10))
Create Cursor authors (Id i, libraryid i, Name C(30))
Create Cursor books(Id i, authorid i, Name C(20), category C(20),Pages N(4), publication C(60))

Insert Into libraries Values(1, "Books",5,"LIB3.1.2","hash")

Insert Into authors Values (1, 1, "Anderson")
Insert Into books Values (1,1,"Legends","Drama",120,"Happer Collins")
Insert Into books Values (2,1,"The Blooms","Fiction",450,"Penguin")

Insert Into authors Values (2, 1, "Sofie Jia")
Insert Into books Values (3,2,"RRR","Fiction",120,"Blue Dart")
Insert Into books Values (4,2,"Heavy Hearts","Documentary",100,"Mcgrath")

Select libraries
Scatter Fields Except id Name root
AddProperty(root,'details[1]')

Select authors
Count For authors.libraryid=libraries.Id To authorcount
Dimension Details[authorcount]
detailindex=1
Scan For authors.libraryid=libraries.Id
   oDetail = Createobject('empty')
   oAuthor = Createobject('empty')
   AddProperty(oAuthor,'name',authors.Name )
   AddProperty(oAuthor,'books[1]')

   Select books
   Count For books.authorid = authors.Id To bookcount
   Dimension books[bookcount]
   bookindex=1
   Scan For books.authorid = authors.Id
      Scatter Fields Name Name oBook
      Scatter Fields category,Pages,publication Name oBookdtl
      AddProperty(book,'bookdtl',oBookdtl)
      books[bookindex] = oBook
      bookindex = bookindex + 1
   Endscan
   Acopy(books,oAuthor.books)

   AddProperty(oDetail,'author',oAuthor) && stupid, but necessary to get "author": in the JSON.
   Details[detailindex] = oDetail
   detailindex = detailindex + 1
Endscan
Acopy(Details,root.Details)

Strtofile( nfjsoncreate(m.root,.T.),'tek-tips.json')
Modify File tek-tips.json Nowait

Which again makes the point that the hierarchical nature of objects best maps to hierarchical data, not joined data flattened to a single list.

The conversion could be more straight forward, if the JSON wouldn't have so many quirks. That details should have been named authors to make a simpler structure is just one of the flaws of this design. A good design would result in simpler code to get to the object. You can directly translate hierarchical data to object that have collections (not arrays, but that's JS) of record objects. Any foreign key is just a pointer to the parent object and when done in object form simply means that record is added to the collection of details of the parent table object.

Last not least I wonder how you got to your Library cursor. It would be the result of a query joining libraries, authors and books, it may result from reading the example json into a cursor. But that removes details about the structure, which makes the list non-reversable without putting in some recipe of how to get back to the hierarchy. The best option is to never kill that hierarchy in the first place and convert the JS object (not the json) to a VFP object and then perhaps derive several tables related to each other like the object->subobject structure relates objectproperties to parent objects.

Chriss

 

[Sng1]

if the JSON wouldn't have so many quirks

This type of format is required by government. So, not much could be done as far as format is concerned. Yes for creating example it have flattened the cursor otherwise 2 tables were available(libraries and books).

You can directly translate hierarchical data to object that have collections (not arrays, but that's JS) of record objects

Ok

 

[Chris Miller]

I totally get that this format is required by government, this is just telling (not the first time) how bad consultants advise such things for the government.

Note that I made a last fix in my code to not include the libraries id in the JSON.

tables were available(libraries and books).

Well, that would already be better, though the library table is quite optional, it's just one record that specifies the last bit of the JSON. I guess that part of JSON is more about the version of the data structure or API that can process this.

But your books are books of authors and as the structure demands one book list for each author, there you see how normalization gives you a grouping by authorid (author name would fail on same name authors btw). An actual database spanning multiple libraries should be even more complex, of course. To assign authors to a library isn't the best idea, that just fits this JSON. authors exist independent from libraries and surely not all libraries have books of all authors, but that would be shared base data, if you'd run a chain of several libraries.

Chriss

 

[mplaza]

Chris, I too noticed the convoluted strutcture, but it's clear it was a structure you would not normally create - meant to be "a short tricky excercise" - that serves its purpose of showing how to create json.

Good point noticing the missing node ( fixed! ).





Marco Plaza
@nfoxProject

https://www.github.com/nftools

 

[Chris Miller]

I guess it could be explained in the bigger picture of where this json is used, but there are at least 3 things I would do different.

Besides the 2 already mentioned, I don't see a good reason to represent a book as name and bookdtl when all four properties could be on the same level. Of course the book title is the main property besides the author and that is the parent for the booklist, but publisher, category, number of pages, they all are simple properties of the book.

It can be felt from seeing this, that it's the result of many meetings of a very bureaucratic nature, where such things are decided.

Chriss