Setting up detail tables with JSON

Detail tables are created when an Extract step is added within a Repeat step. The Repeat step goes through a number of lines or nodes. An Extract step within that loop extracts data from each line or node.
How exactly this loop is constructed depends on the type of source data.

This topic explains how to set up detail tables when extracting data from a JSON file.

How to extract transactional data that is not structured uniformly is explained in another topic: Extracting data with an Action step script.
The topic: Extracting data of variable length explains a few ways to extract data from a variable number of lines into one data field.

For more information about detail tables, multiple detail tables and nested detail tables, see Detail tables.

Creating the loop

In a JSON file, transactional data appear as repeating items in an array or object.

1. Move the cursor to the parent element of the repeating items. By default the cursor is located at the top of the page, but previous steps may have moved it.

   If the cursor is already in the correct position, you can skip this step.
   Note that an Extract step does not move the cursor.

   To move the cursor you can use a Goto step

   1. Select the parent element of the repeating items.

   2. Right-click and select Add Goto. The Goto step will move the cursor to the start of the first line item.
      

2. Right-click the opening bracket { of the first of the repeating items and select Add Repeat. This adds a Repeat step to the data mapping configuration.
   By default, the Repeat type of this step is set to For Each. With this setting the Extract step goes through the defined collection of items, without the need for a Goto step within the loop. (With the other Repeat type settings the loop must contain a Goto step.)
   

   You can find the corresponding JsonPath in the JsonPath Collection field on the Step properties pane, as long as the Repeat step is selected on the Steps pane.

   Tip: You may edit the JsonPath in the JsonPath Collection field to include or exclude elements from the loop. For an overview of the JsonPath syntax, see https://github.com/json-path/jsonpath. One example of this is given in a How-to: XML/JSON loops.

3. (Optional.) Add an empty detail table via the Data Model pane: right-click the Data Model and select Add a table. Give the detail table a name.

4. Select the Repeat step on the Steps pane.

5. Extract the data: inside the first of the repeating items, select the data that you want to extract. Then drag & drop them in the Data Model, or right-click and select Add Extraction.
   When you drag & drop data on the name of a detail table in the Data Model pane, the data are added to that detail table. Dropping the data somewhere else on the Data Model pane creates a new detail table, with a default name that you can change later on (see Renaming a detail table).
   

   The new Extract step is located inside the Repeat step.

6. Select the Extract step and make sure that the corresponding JsonPath in the JsonPath Collection field on the Step properties pane is relative.
   A relative path starts with . which is the current element.
   An absolute JsonPath starts with $ which is the root.

About JsonPath

The DataMapper moves through JSON files using JsonPath, a path-like syntax to identify and navigate elements in a JSON document. For an overview of the JsonPath syntax, see https://github.com/json-path/jsonpath. One example of this is given in a How-to: XML/JSON loops.

In the DataMapper the JsonPath can be absolute (start with $ which is the root) or relative to the current position (start with . which is the current element).

A JsonPath can be relative or absolute. Note, however, that with a relative JsonPath going up to a parent element is not possible.

Tip: The full JsonPath to an element is displayed at the bottom left of the window when you select it. To copy the path, right-click it and select Copy.

Note: If a key in a JSON file has a name that looks like a function (e.g. "TLIST(A1)"), then the Extract step has to use a JsonPath with bracket notation instead of the default dot notation. For information about the bracket notation see https://goessner.net/articles/JsonPath/.