JSON Data Type

JSON Data Type

JSON Data Type

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:



  • $ 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:

How to extract the entire structure of an object inside your data and store that as a JSON string in a field

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.


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:

You can craft your JSONPath statement to create a subset of the array and loop only through that subset, and not through every single element in a array.

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.


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


Tagged in: JSON, JSONPath

Leave a Reply

Your email address will not be published. Required fields are marked *

Similar posts you might also like

PDF/A-3 and e-Invoicing

PDF/A-3 and e-Invoicing

e-Invoicing standards are cropping up everywhere and are increasingly becoming mandatory. A number of those standards are based on PDF/A-3 with an XML attachment. Starting with version 2019.1, OL Connect comes with features for creating PDF/A-3, and turning them into ZUGFeRD and Factur-X conforming e-invoices. PDF/A-3 is not only for e-invoicing, it’s an archive format after all, so other applications become possible as well, of course. This article explains how to use this functionality in OL Connect.


Naming convention for Data Fields

Naming convention for Data Fields

As a general rule, software engineers love to give users of their applications as much flexibility as possible. If you ask them for an option allowing you to pick one of the seven rainbow colors, they’ll usually respond with some way of picking any of the 32 million colors that your display is capable of. After all, they say, it’s no more work for them to do so and it gives you, the user, much greater flexibility. But that flexibility may come at a price you’re not prepared to pay.


Breaking out of loops in DataMapper

Breaking out of loops in DataMapper

Loops are commonplace in data mapping configurations. Unless all your data is static, you have to use them in order to extract repeating items. However, it can sometimes be challenging to loop precisely on a specific set of conditions, especially when dealing with compound conditions. Let’s see how OL Connect 2021.1 can help us streamline loops.