Getting information from JSON opens up FileMaker for more integration with web services and RESTful APIs.
With the Data API (beta in 16, v1 in 17) and Admin API (FileMaker Server 17), JSON will likely continue to become more important and more integrated with FileMaker.
Querying JSON Data Elements
The JSONGetElement function lets us query specific JSON data elements, using either an object name, an array index, or a path. The key is there that these must be known values. We need to either know something about the JSON structure if we want specific elements. We cannot query or search JSON based on certain criteria.
Alternatively, we could get everything from the JSON object into a field or variable, and then apply some rules after the fact. Maybe we want to compare information or look for elements that fit certain requirements, such as a price above or below certain thresholds. Maybe we want to look for arrays that are missing certain elements, such as ISBN numbers in an array with book information.
In XML there’s a syntax called XPath that uses expressions to find information in nodes or node-sets. There’s a similar concept that allows us to extend JSON, taking advantage of a JavaScript library called JSONPath, and FileMaker’s own Web Viewer to act as the engine for the JavaScript library. This lets us feed in JSON and a JSONPath expression into a Web Viewer, and return the result via the fmpurl protocol.
FileMaker and Web Viewer Integrations
Web Viewer and JavaScript integration is nothing new. A Google search for FileMaker and JavaScript integration will likely bring up many examples of such integration. Some examples include FileMaker reaching out to external APIs. Other examples use what’s called a “data uri” to render web pages directly in your web viewer. Using a “data uri” you can include CSS and JavaScript in your HTML code.
Setting Up Your JSON Path
The included demo file has a field for our jsonpath.js library. This is an open source file and our only dependency. When implementing this in a real solution, you’d likely place this in a resource table and only call it as needed.
Next, we have a field for our JSON. This can be an object or array. Your own process may store the JSON in a variable, but the demo file uses a field so that we can see the source values as we’re testing our JSONPath expressions.
Additionally, we have an “input” field, which is the JSONPath query. Instead of JSONGetURL, JSONPath uses expressions in a text format to search the JSON for matching results.
Then, we have a result field so we can see the result of our JSONPath query for certain values from the JSON using the jsonpath.js library. Both the input and result fields also could be turned into variables.
Finally, we have an empty Web Viewer object, with a name of “WebViewer” so we can point to the object as needed.
Scripting Your JSON Path
There are two short scripts, “JSONPathInput” and “JSONPathWrite.”
The first script, “JSONPathInput,” builds HTML and sets this HTML into the Web Viewer. As mentioned above, we can use “fmpurl” inside HTML to call FileMaker scripts. The last action in our “JSONPathInput” script does just this, calling the “JSONPathWrite” with a script parameter. That script parameter is the result of the JSONPath query, and all the script does is set our Result field with this value. The process is simple enough, but the complexity and power lie in the HTML that we create.
HTML
The HTML inside the script takes up only a few lines of code. (Note: The characters are there to escape double quotes inside the text.)
Substitute ( "data:text/html,
<!DOCTYPE html>
<html lang=\"en\">
<head>
<script type=\"text/javascript\">
JS_FUNCTION
</script>
<script type=\"text/javascript\">
var json = JSON;
var p = jsonPath(json, \"INPUT\" );
var r = JSON.stringify(p);
var url = \"fmp://$/" & Get ( FileName ) & "?script=jsonPathWrite¶m=\" + r ;
window.location = url ;
</script>
</head>
</html>"
; [ "JS_FUNCTION" ; zResources::JSONPath ]
; [ "JSON" ; zResources::JSON ]
; [ "INPUT" ; zResources::Input ]
)Let’s break this apart into smaller chunks.
First, the HTML is wrapped inside a FileMaker substitute function. Skipping briefly to the last lines of code, we see three substitutions. Each bit of text bracketed by the double asterisks gets switched out with values from our fields. These three values, such as **INPUT**, appears in the HTML code as placeholders. We’ll tackle each piece separately.
Our text starts with “data:text/html,” which means we’re constructing a web page.
Next, we have some HTML specific tags at the beginning and end:
<!DOCTYPE html>
<html lang=\"en\">
<head>
.
.
.
</head>
</html>Inside this HTML we have two JavaScript functions. The first function, represented by our **JS_FUNCTION** placeholder, represents the jsonpath.js library. This brings the library into our web page.
The second function is where we write out how to apply the jsonpath.js library against our JSON and our JSONPath expression.
<script type=\"text/javascript\">
var json = **JSON**;
var p = jsonPath(json, \"**INPUT**\" );
var r = JSON.stringify(p);
var url = \"fmp://$/" & Get ( FileName ) & "?script=jsonPathWrite¶m=\" + r ;
window.location = url ;
</script>We declare our JSON value as a variable inside JavaScript.
Then we declare another variable with the result of sending our **INPUT** or query from the field into the JSONPath function.
Ordinarily, this might be enough, but sometimes JSONPath returns objects or arrays, and these might not display properly in certain browsers. Instead, you might see a value like “Object object,” so we use the built-in “JSON.stringify” function to convert objects into strings. Finally, we set a variable to the fmpurl address of our “JSONPathWrite” script, with the stringified variable as the script parameter, and then feed this url into the Web Viewer.
How does JSONPath work?
Let’s assume we have some JSON:
{ "store": {
"book": [
{ "category": "reference",
"author": "Nigel Rees",
"title": "Sayings of the Century",
"price": 8.95
},
{ "category": "fiction",
"author": "Evelyn Waugh",
"title": "Sword of Honour",
"price": 12.99
},
{ "category": "fiction",
"author": "Herman Melville",
"title": "Moby Dick",
"isbn": "0-553-21311-3",
"price": 8.99
},
{ "category": "fiction",
"author": "J. R. R. Tolkien",
"title": "The Lord of the Rings",
"isbn": "0-395-19395-8",
"price": 22.99
}
],
"bicycle": {
"color": "red",
"price": 19.95
}
}
}With JSONGetElement, to pull out the ISBN we’d need to loop through the “book” array and extract the value using something like:
JSONGetElement ( JSON ; "store.book[2]isbn" )The [2] represents the 3rd index in the book array. The first two indexes contain no value for ISBN, but we’d need to loop through all of them to pull out what we need.
With JSONPath, all queries begin with “$” followed by a path with optional operators. The “$” represents the root object or element. Dot and bracket notation is used in ways similar to the JSONGetElement function. The power of JSONPath comes in the ability to traverse JSON and slice out elements, with the logic built into the JSONPath expression.
Say you need to know which elements in the above JSON have an ISBN value. You can get the array values with this query.
$..book[?(@.isbn)]If you want to find titles of books that don’t have an ISBN, then you flip this around and append the title key:
$..book[?(!@.isbn)].titleTo find books where the price is greater than some value, you can use comparison operators.
$..book[?(@.price > 15)]This “stringify” action in our JavaScript function will wrap your result inside a JavaScript array, so some additional manipulation is required. If your result contains a single value, even if this is object, you can use JSONGetElement ( Result ; “[0]” ) to strip off the “[ ]” brackets. For arrays that contain multiple values, you can use JSONListKeys to determine how many values there are in your array and process these with the native JSON functions.
This query returns one book object where the book costs more than 15:
$..book[?(@.price > 15)]
[{"category":"fiction","author":"J.R.R.Tolkien","title":"The Lord of the Rings","isbn":"0-395-19395-8","price":22.99}]In the above example, to get the title, use JSONGetElement ( Result ; “[0]title” ) and this will return “The Lord of the Rings.”
For a more complete review of all options and operators available in this JSONPath Javascript library, refer to this article on JSONPath.
Get the Demo file
Why Use JSONPath?
There is some overlap with JSONPath and JSONGetElement.
$.store is the same as JSONGetElement( JSON; “” ) and returns our entire JSON. FileMaker reorders JSON based on the keys, so they might look different, but it’s the same content.
JSONPath allows us to extend our applications that use JSON to both analyze and extract information, almost like searching the content, without first pulling apart as we’d need to do with JSONGetElement and additional scripting.
Caveats with JSONPath
There are a couple of considerations with fmpurl. Security settings will need to be updated to allow the “fmpurlscript” extended privilege set for any accounts that use this process.
If you have multiple versions of FileMaker open, running the script with the “wrong” version may fail. If the fmpurl is bound to FileMaker 16, and you try to use it in 17, you might get an error.
Leveraging New Features in FileMaker 17
If you have any questions about how to benefit from JSONPath and other new features in FileMaker 17, please contact our team. We’re happy to help your team determine the best way to leverage new functionality for your FileMaker solution.
