Wouldn’t you just love to use the contents of a data field as if it were an object, with its own properties and arrays, and then quickly display that object as a detail table in your template without much effort? If you answered yes to that question (and who wouldn’t ?!?), then this post is for you!

JSON in the DataMapper

If you elected to read this post, it means you not only know what JSON is, but you have likely used it in your projects. So I’ll skip the part where I explain what JSON is. Suffice it to say that in today’s world, it is by far the most popular way of exchanging information across applications and servers.

OL Connect’s DataMapper was already able to read JSON files to allow you to quickly extract values from it. But sometimes, you don’t want to extract each individual value: you just want to grab one of the objects or arrays in the JSON data and store it as a single value that you can use later on in a script to populate multiple elements in your template.

With OL Connect 2022.1, we introduced a new JSON field type in the DataMapper. Using it is deceptively easy: just drag one of the JSON elements over to the data model pane and drop it there. The new field is automatically set to JSON and the entire contents of the element are stored in that single field.

I wish I could tell you more about it, but really, in the DataMapper, that’s pretty much all you need to know.

Ahhhh… but how useful is it, you ask?

I’ve created a very basic example of how easy this new feature makes the creation of detail tables, in conjunction with the Handlebars feature introduced in the OL Connect 2022.1 Designer (which you must read about in this excellent article by my friend Erik).

Use case

I’m a Formula One fan, always been, always will be. The kind that deeply believes Lewis Hamilton was robbed of his title on the last race of the 2021 season (I’m probably not making any friends here, especially amongst my Dutch colleagues!). But that’s another topic altogether. 

So I found this site offering a public API that allows me to get any kind of statistics for any race, driver or team since the first F1 season in 1950. Just for fun, I wanted to see if I could create a list of all world champions since then, along with the teams they were driving for. Yes, I know I could have just looked it up in Wikipedia, but what fun would that have been? And besides, what if I had wanted to list all drivers who finished 7th in a race since 1950? Would I have found that in Wikipedia? I think not!

So anyway, I queried this API and received a response packaged as a JSON object, as is usually the norm with REST APIs. The structure is pretty straightforward and I thought it would be a cinch to process it in the DataMapper using the standard JSON data type (but without using the JSON field type). 

All I wanted was each driver’s first and last name, the year they won the championship and the teams for which they drove on that year. But it turns out it’s not as easy as I thought. You have to loop through all champions, and then for each champion loop through all the Teams (because drivers can switch teams mid-season). Not overly complicated, but still a bit of a pain. And you end up with nested detail tables, which doesn’t make things any easier in the Designer.

So instead of going at it the classic way, I used the JSON field type feature by simply drag&dropping the entire StandingsLists array (highlighted above) into a single field. No detail tables, no loop. Just a single extraction step:

It honestly cannot get any simpler than that!

So now that I was all done all with this highly clever algorithmic masterpiece (!), I headed over to the Designer where I used the Handlebars feature to create a list that looks like this:

Granted, it’s not very pretty, but I am notoriously bad at creating visually appealing documents. Besides, what do you expect when the entire HTML source code for the Section looks like this:

<table id="table" data-column-resize="" style="width:100%" data-expander="2019">
  <thead>
    <tr style="background-color:silver">
      <th style="text-align: left; width: 25%;">Year </th>
      <th style="text-align: left; width: 25%;">Driver </th>
      <th style="text-align: left; width: 25%;">Team </th>
    </tr>
  </thead>
  <tbody id="drivers">
  </tbody>
</table>

… and when the entire script that fills out the table – another masterpiece of creativity – looks like this:

results.html( Handlebars.render('snippets/DriverTable.hbs', record.StandingsLists) ); 

Yes, that’s the entire script.

Notice how I am passing my JSON field as a parameter to the Handlebars.render() method. That’s because the method expects an object and, well, that’s what the StandingsLists field contains!

And if we take a look at the DriverTable snippet, all it contains is the following code:

{{#each this}}
  <tr>
    <td>{{season}}</td>
    <td>{{DriverStandings.0.Driver.givenName}} {{DriverStandings.0.Driver.familyName}}</td>
    <td>{{DriverStandings.0.Constructors.0.name}}</td>
  </tr>
{{/each}}

And that’s it. The snippet iterates on the array elements contained in the object it received, and fills the table with the appropriate information.

oh… and by the way

I haven’t even mentioned the fact that when using scripts in the Designer and the DataMapper, you can access each of the elements inside the JSON field as if they were native properties, with code completion included:

Conclusion

There is no possible conclusion other than: this feature is really cool, simple and efficient, especially when used with Handlebars!