JSON Serialization Formats

The wwJSONSerializer class serializes FoxPro values in JSON formatted strings. This topic demonstrates how to create output for various types and what the output looks like:

String

Let's start with a simple string serialized into JSON:

DO wwJsonSerializer && Load Libs
loSerializer = CREATEOBJECT("wwJsonSerializer")
lcJSON = loSerializer.Serialize("Hello Rick")

returns: "Hello Rick" (including the quotes)

Dates

Dates are serialized and parsed in ISO format which looks like this:

loSerializer = CREATEOBJECT("wwJsonSerializer")
lcJSON = loSerializer.Serialize(DateTime())

returns: "2011-01-08T04:34:56Z"

There's also an option flag that supports Microsoft's old MSAJAX style serialization format.

Numbers and Booleans

All other simple types are created using standard JavaScript literals for types. Numbers are created as simple number values (decimal style). Booleans are stored as true and false.

Objects

You can also serialize objects. Here's an example of a hierarchical object:

loCustomer = CREATEOBJECT("EMPTY")
ADDPROPERTY(loCustomer,"Company","West Wind Technologies")
ADDPROPERTY(loCustomer,"Name","Rick Strahl")
ADDPROPERTY(loCustomer,"Entered",DATETIME())

loAddress = CREATEOBJECT("EMPTY")
ADDPROPERTY(loAddress,"Street","32 Kaiea")
ADDPROPERTY(loAddress,"City","Paia")
ADDPROPERTY(loAddress,"State","HI")
ADDPROPERTY(loAddress,"Zip","96779")
ADDPROPERTY(loAddress,"Occupants",1)

ADDPROPERTY(loCustomer,"Address",loAddress)

loSerializer = CREATEOBJECT("wwJsonSerializer")
loSerializer.FormattedOutput = .T.  && prettify the JSON
lcJSON = loSerializer.Serialize(loCustomer)

which yields:

{
  "address": {
    "city": "Paia",
    "occupants": 1,
    "state": "HI",
    "street": "32 Kaiea",
    "zip": "96779"
  },
  "company": "West Wind Technologies",
  "entered": "2015-10-27T22:41:17Z",
  "name": "Rick Strahl"
}

Formatted Output

Note that this is formatted output which is created when the .FormattedOutput property is true. By default the serializer produces unformatted output, which is a single line of text.

All properties are created inside of JavaScript object notation () and an inner object is created for the address. As you can see JSON is self-describing in that there's no schema required - the format lets JavaScript know what type you're dealing with.

Proper Case Property Names

By default all object properties are rendered in lower case due to FoxPro's inability to return properly cased generic runtime type information.

You can optionally override individual property names generated using the .PropertyNameOverrides property.

loSerializer = CREATEOBJECT("wwJsonSerializer")

loSerializer.FormattedOutput = .T.
loSerializer.PropertyNameOverrides = ;
  "Address,City,Occupants,State,Street,Zip,Company,Entered,Name"

lcJSON = loSerializer.Serialize(loCustomer)

Simply create a comma delimited string that includes all the property names you want to override, using the proper case you'd like to see. Note that you only need to provide those properties that need to be overwritten.

Internally this replaces all the lower case properties with the explicitly provided properties:

{
  "Address": {
    "City": "Paia",
    "Occupants": 1,
    "State": "HI",
    "Street": "32 Kaiea",
    "Zip": "96779"
  },
  "Company": "West Wind Technologies",
  "Entered": "2015-10-27T22:43:02Z",
  "Name": "Rick Strahl"
}

Keep in mind that most JavaScript objects are expected to be camelCased - meaning lower case first word with upper cased subsequent words (ie. camelCase, birthDate, firstName, propertyNameOverrides etc.) so typically you need to apply name overrides only only longer, multi-part words.

FoxPro Cursors or Tables

You can also serialize cursors, which are rendered as JSON arrays. These cursor arrays can also be attached as child arrays to objects.

To serialize a cursor you use a special syntax in the call to Serialize by using cursor:Alias:

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")

The result of this serialization looks like this:

[
  {
    "id": "_4FG12Y7U6",
    "firstName": "John",
    "lastName": "Doe",
    "company": "Avionics Inc.",
    "careof": "John Doe",
    "address": "PO Box 1907\r\nManassas, VA 22110-0801",
    "shipAddr": "",
    "email": "",
    "phone": "808 123-0001",
    "billRate": 150,
    "btype": "Labor_PRG",
    "entered": "2013-06-12T09:46:40Z",
    "state": ""
  },
  {
    "id": "_4FG12Y7TX",
    "firstName": "Steve",
    "lastName": "Herbin",
    "company": "Avis World Headquarters",
    "careof": "Steve Herbin",
    "address": "111 Old Country Road\nGarden City, NY 11530\n",
    "shipAddr": "",
    "email": "",
    "phone": "(516) 222-3743",
    "billRate": 55,
    "btype": "Labor_PRG",
    "entered": "2013-09-10T09:46:40Z",
    "state": ""
  },
 {
    "id": "_4FG12Y7TQ",
    "firstName": "Dan",
    "lastName": "Mullen",
    "company": "XVC Solutions",
    "careof": "Dan Mullen",
    "address": "22xx Tudor Lane\r\nStow, OH 44xx",
    "shipAddr": "",
    "email": "",
    "phone": "",
    "billRate": 80,
    "btype": "Labor_PRG",
    "entered": "2013-10-19T09:46:40Z",
    "state": ""
  },
  {
    "id": "_4FG12Y7U4",
    "firstName": "Kim",
    "lastName": "Westin",
    "company": "XYZ Systems house",
    "careof": "",
    "address": "9411 Broadway\r\nNew York, NY 11111",
    "shipAddr": "",
    "email": "",
    "phone": "444-4444",
    "billRate": 150,
    "btype": "Labor_PRG",
    "entered": "2013-07-02T09:46:40Z",
    "state": ""
  }
]

As with object output, you can use .PropertyNameOverrides to override the names and case of the property names.

Legacy Format

The format of the default cursor:cursorName rendering has changed to return just the array. The legacy format returned a top level object with Rows array and Count property. You can still use this format by using cursor_legacy:cursorName.

Mixing Objects and Cursors

You can also nest cursors into objects - if you want to create a child array as part of an object, make sure that the cursor to be attached exists and then simple create the property name and assign cursor:Alias to the property as a string. When the serializer runs, it'll expand the cursor to JSON and embeds it into the document.

An example of this is to create a custom JSON response for something like jqGrid, which requires an object structure and an array structure for the data to display. The following code demonstrates how to create this structure:

SELECT Company, Careof FROM wwDemo\tt_cust INTO CURSOR TQUery

loGrid = CREATE("EMPTY")

AddProperty(loGrid,"page",1)
AddProperty(loGrid,"total", Reccount())
AddProperty(loGrid,"records",10)

*** Attach the cursor as an Array property
AddProperty(loGrid,"rows","cursor:TQuery")

loSer = CREATEOBJECT("wwJsonSerializer")
loSer.Formatted = .T.
lcJSON = loSer.Serialize(loGrid)

ShowText(lcJson)

This creates a structure like this:

{
   page: 1,
   total: 100,
   records: 10,
   rows: [
        {
            company: "West Wind",
            careof: "Rick"
        },
        {
            company: "EPS",
            careof: "Markus"
        }
   ]
}

Note the rows property and the assignment of cursor:TQuery which generates just the array in the object without the Rows object prefix.

Arrays, Collections and wwCollection objects

wwJsonSerializer serializes FoxPro arrays, Collections and wwCollection objects into JSON arrays. Key Value collections or wwKeyValue collections are rendered as JSON object maps where the keys are mapped to JSON properties of the object.

Array elements can be of any other type supported including object and other arrays. Arrays can be of a single type or mixed types as well.

Arrays are serialized as FoxPro Collections, which have a count property and an Item(x) method to retrieve items.

Here's a simple string array:

lcJson = '["Value,1","Value2. This \"is\" neat","Value3"]'
? lcJson
loResult = loJson.Deserialize(lcJson)

*** Result is a Collection
? loResult.Count
FOR lnX = 1 TO loResult.Count
   ? loResult.Item(lnX)
ENDFOR

? "*** Re-serialize String collection"
? loJson.Serialize(loResult)  && Same as original string

Another example using an object array:

? "*** Object array"
lcJson = '[{"name":"Rick","company":"West Wind","Balance":100.05},' + ;
         '{"name":"Markus","company":"EPS Software","Balance":1100.05},' +;
         '{"name":"Kevin","company":"OakLeaf","Balance":1300.05},' 
loResult =loJson.Deserialize(lcJson)

? loResult.Count
FOR lnX = 1 TO loResult.Count
   loCust = loResult.Item(lnX)
   ? loCust.name, loCust.company, loCust.Balance
ENDFOR

? "*** re-serialize object array"
? loJson.Serialize(loResult)  && same as above

You can also have arrays with mixed items: simple values, objects and even other objects.

? "*** Mixed items"
lcJson = '["Value" , {"name":"Rick","company":"West Wind"} , ["rick","markus"],"Test",10,null,"Test1"]'
?lcJson
loResult =loJson.DeserializeJson(lcJson)
? loResult.Count
FOR lnX = 1 TO loResult.Count
   ? loResult.Item(lnX)
ENDFOR

? "*** Re-serialize loResult"
? loJson.Serialize(loResult)

No built-in Two Way Cursor Serialization

The deserializer works only with objects. While you can serialize cursors, there's no way to turn the JSON back into a cursor as there's no schema information that determines the types used. If you deserialize a JSON serialized cursor - which is simply a JSON array - you get back a collection.

However, it's fairly easy to capture the collection and get it back into a cursor. If you have an existing cursor with the matching structure you can use CollectionToCursor().

SELECT * FROM customers ;
  INTO CURSOR TCompanies READWRITE

*** Capture JSON from cursor
lcJSON = loSerializer.Serialize("cursor:TCompanies")

CLOSE DATA

*** Deserialize JSON into Collection
loCol = loSerializer.DeserializeJson(lcJson)

*** Create an empty cursor to import to
SELECT * from Customers ;
       INTO Cursor TCompanies READWRITE ;
       WHERE .f.   

*** Import data from the Collection
CollectionToCursor(loCol,"TCompanies")

© West Wind Technologies, 1996-2019 • Updated: 04/08/16
Comment or report problem with topic