Objectif Lune joins Upland Software.Learn more >
December 8th, 2021
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.
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:
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).
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.
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-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
Receive exclusive OL products news, tips and resources.
Your email address will not be published. Required fields are marked *
Notify me of followup comments via e-mail. You can also subscribe without commenting.