fmQBO Working with JSON

WORKING WITH JSON

JSON is text based data format.  So at the end of the day you either going to be parsing the data you want out of a text string that represents an Invoice or Deposit or some other Quickbooks entity. Or you are going to be building a text string that represents one.

Doing this by hand would be no fun. But we have some custom functions that you can use to make this much easier.

JSON is made of objects, object properties, arrays, and array items.  Objects are made up of a set of object properties each of which is a lot like a name value pair.  The list of properties in an object have no set order. They may show up in any order. Arrays on the other hand have set set order. They sort order does not change.

JSON Object properties can be strings, integers, decimals, boolean, arrays or even other objects  And an array can contain objects.  A set sorted records can be represented in JSON as an array of object.

Here is an example represent a person and list of their pets.

{
	"firstName": "Todd",
	"lastName": "Geist",
	"pets": [
		{
			"species": "dog",
			"name": "Kona"
		},
		{
			"species": "dog",
			"name": "Alfie"
		}
	]
}
	
view raw gistfile1.txt hosted with  ❤ by  GitHub

Building JSON in FileMaker

Declarative Approach

For fmQBO we came up with a set of Custom Functions backed by the BaseElements plugin that can make it easier to build JSON.  These first four use a declarative approach, which sort of means that you declare everything in a single shot.

  • jsonO(jsonObectProperties) – takes a string of Object Properties and creates a JSON Object
  • jsonOp(name ; value) – create a properly encoded json object properties
  • jsonA(jsonArrayItems) – takes a string of Array Items and creates a JSON Array
  • jsonAv( arrayValue ) – create a properly encoded json array values

The same JSON we saw above can be constructed by the following FileMake function using these custom functions:

view raw jsonCfs hosted with  ❤ by  GitHub

This approach works great, but sometimes you need to build JSON in steps or in pieces.  Maybe because you don’t know exactly what the finished JSON will look like when you start.  Perhaps the business logic requires that you conditional build up the JSON based a number of factors.  You can certainly do that with the declarative approach but it can get ugly quickly.  Pretty soon you will loose all the benefits of the declarative approach.

Sometimes you want to modify some json that you get from somewhere else. Looking back the examples above.  Imagine you have a variable $person with the JSON above and you just want to change the second pet’s name to “Rin Tin Tin”.  If you wanted to do that declaratively, you would have to start over and re-build the entire thing. We need another approach

Imperative Approach

An imperative approach allows you to build up JSON one property at a time or modify one property at a time of an existing JSON object or array.  We have a function for this called jsonModify(json ; path; value).

If we had or person JSON from above in a variable called $person and we just wanted to change the second pets name to”Rin Tin Tin” we would just do this:

jsonModify($person ; “pets[1].name” ; “Rin Tin Tin”)

The second parameter, “pets[1].name”, indicates that we are to modify name of the second pet’s name.  JavaScript and JSON have zero based indexes. Thats why we see a [1] here instead of [0].  This is what is known as JSONPath. It’s a w ay of describing the specific property or array value you want within a JSON string.  The Base Elements Plugin supports JSONPath for parsing bits out of JSON. Our function jsonModify() goes the other direction. It lets you modify specify bits inside an existing JSON string.

With this approach, you can build up a JSON object from nothing as well. All I have to do is start with an empty Object “{}”. So jsonModify(“{}”, “name.first” ; “todd”) will create a JSON object that looks like this:

 {“name”:{“first”:”todd”}}

I could store that in a var $json with a Set Var Step, and then add another property in the next Set Var Script Step with this, jsonModify($json, “name.last” ; “geist”).  Now we would have this.

{“name”:{“first”:”todd”,”last”:”geist”}}

This turns out to be a very powerful way to build up JSON string. Its we end up doing most of the time with fmQBO.

Parsing JSON

Obviously you will need to parse the JSON you get back from the API so you can store data in your database.  Its really hard to parse JSON by hand, once you get past the simplest case. Luckily we have the Base Elements Plugin to rely on.  The plugin has a BE_JSONPath() function which can be used to parse JSON.  We also created a function called jsonGet(json ; path) that makes this a little simpler

We saw an example of using a “path” to target specific properties within the JSON string when we were discussing jsonModify(). This works exactly the same way.  Going back to our person JSON object from above, look at following examples:

  • jsonGet($person ; “firstName) = “Todd”
  • jsonGet($person ; “lastName”) = “Geist”
  • jsonGet($person ; “pets[0].name”) = “Kona”

As you can see the “path” lets you pick out individual properties from the JSON.  The square brackets let you target a specific array value. Remember again that JSON has zero based indexes so [0] in this case means the first “pet”.  Using a combination of “dots” ( “.” ), square brackets and property names, you can grab any piece of any JSON String.

Miscellaneous Functions

jsonDelete(json ; path) does pretty much what you’d expect. It removes the property or array value specified by the path.

jsonPrettyPrint(json) just prints the JSON string with indents and white space so it it easier to read.  It’s useful when you are debugging. It’s important to note that APIs, JavaScript and JSONPath, do not care about white space. It is ignored.  So this function is really only to make things easier on the human eye.

Differences from Common FileMaker Rules

There are a couple of very important differences between how JSON works and we commonly think of FileMaker working.  The first one is that it has zero based indexes.  If you want the first value in an array then you use 0. If you want the the third value, you use 2.  FileMaker doesn’t really have array handling so we don’t see this.  Interestingly however, the Choose function in FileMaker is zero based as well.

The second one is that JSON properties are case sensitive.  {“firstName” : “Todd”}  is not the same as  {“FirstName” : “Todd”}.  If you try to use jsonGet($json ; “firstName”) on {“FirstName” : “Todd”} you will get nothing (“”);

Reference

The JSON Custom Functions are documented  here. Check them out for more info.

Still need help? Contact Us Contact Us