Just like in the Friday the 13th movie franchise, JSON is everywhere ( :-|). And now, it can be handled natively as a new data type in the DataMapper. All those web-based processes that use JSON as a data exchange format no longer require you to convert the data to something else in order to extract values from it. Read on to learn more on this feature we introduced with version 2021.1 of OL Connect.

Note: all examples shown use resources that are attached at the bottom of this article.

Start with JSONPath

JSON is easy to use from inside any JavaScript engine. But you probably wouldn’t want your entire data mapping to contain only scripts. So you need a way to target, filter and select specific elements in the JSON structure without scripting.

Enter JSONPath: a query language, similar to XPATH for XML, that allows you to do just that. Now don’t be deterred by the reference to XPATH! Most of the JSONPath syntax can be learned in a matter of minutes, as opposed to days for XPATH.

Just like you use XPATH to create a collection of target items in XML, you use JSONPath to do the same with JSON. For instance, to select all products in an invoice whose shipped property is greater than 2, you would use something like this:

$.invoices[0].Products[?(@.Shipped>"2")]

Where:

• $ is the root element of the structure
• .invoices[0].Products is the array of Products in the first Invoice
• [?()] is a conditional filter to apply to each Product in the array
• @ is the current element in the array being filtered
• .Shipped>”2″ is the condition to apply to each element

Note that JSONPath is case-sensitive. So .Invoices is not the same as .invoices. 

Once you understand the above statement, you’ve pretty much learned 90% of what you need to know about JSONPath. There are other goodies you can use as well, and there are fortunately plenty of resources on the web that will teach you all about them (e.g. this one describes the syntax, and you can practice with this an online JSONPath tester).

Extracting data

This part is pretty much as straightforward as you’d expect: highlight an element and drag it over to the Data Model or press F6 to create a new extraction task.

There is, however, a cool additional feature that allows you to extract the entire structure of an object inside your data and store that as a JSON string in a field. For instance, to extract one product into a single field in the data model, just select either the starting or ending curly brace and drag that over to the data model:

And yes, you guessed it, if we had selected the Products[] element just above that, we would have extracted the entire array of products into a single data field. In other words, you can extract any JSON structure to a single field.

Now of course, you can still extract each individual member of that object into its own data field, but depending on what you intend to do with the values in your template, this feature might be useful, in particular to populate Data Tables objects in your templates.

Looping

This is where JSONPath shines: you don’t have to loop through every single element in an array. You can craft your JSONPath statement to create a subset of the array and loop only through that subset. Going back to the example given earlier, let’s imagine we only want to extract Products whose Shipped property is greater than 2:

And by the way, you might not even want to loop through that array! Remember how we already mentioned you can extract any JSON structure into a single field? Well you could extract this filtered collection with just a single Extract step. Set its JSONPath property to the same expression we used for the Loop and the entire collection is saved inside a single field in the data model.

Memory usage and performance

Memory-wise, JSON is much more efficient than XML. This means each individual record can be much larger than its XML equivalent. In fact, when dealing with large XML records, you might find it more efficient to convert them to JSON (using, for instance, Workflow’s XML to JSON conversion task) and feeding the result into the DataMapper instead of XML.

Navigation inside JSON is also lightning fast. JSONPath can create subsets almost instantaneously, regardless of the amount of data in each record.

Recap

Memory-efficient, fast, easier to master and no longer requiring conversion of data files. Native JSON support has it all, have fun using it!

Resources