JSON to Table

• Task type: json-to-table

The "JSON to Table" task allows you to import data from a tabular JSON file and insert it into a database table. It serves a dual purpose: it connects to a database to physically write the data and, simultaneously, exposes the imported data as a Dataset for use by subsequent tasks in the Workflow (such as an Iterator). This task operates on incoming JSON provided by a previous task (e.g., "Import Text").

1. Database Connection

In this tab, you can configure all parameters required to establish a connection to the target database.

Target Table

You can define the source of credentials for the connection:

• Manual: allows the manual entry of all connection and table data into the fields below.
• Environment: allows the use of a preconfigured connection environment. By selecting one, the authentication fields are automatically populated.

Database Type

Defines the type of database (DBMS) to connect to. Supported options include:

• PostgreSQL
• SQL Server
• MySQL
• Oracle DB: allows you to specify the connection via SID or SERVICE.
• SAP HANA

SSL Connection

Indicates whether the connection to the database must occur via a secure channel.

Authentication

Field	Description
Host	Address of the authorised external database.
Port	Port expected by the connection to the external database.
DB Name	Name of the database to connect to.
User	Username to access the database.
Set Password	Opens a window to enter the password securely.

Table Management

Defines how the task must interact with the physical table in the database.

1. Target Table: determines the action to perform on the table:

• Create Table: always creates a new table. The operation fails if a table with the same name already exists.
• Create Table if Not Exists: creates a new table only if a table with the same name does not already exist.
• Existing Table: inserts data into an existing table. By default, new data are appended to those already present (Append). By activating the Truncate Insert checkbox, the table is emptied before inserting the new data.

2. Target Table Name: name of the table to create or into which to write data (e.g., users, products, log_events).

2. Tabular Structure

The Tabular Structure tab allows you to define the fields (columns) of the table and map each column to elements in the source JSON file.

Available Fields

Each row represents a column in the table. For each field, you can configure:

Field	Description
Type	Data type of the field. Available values: NUMERIC, STRING, DATE, DATE AND TIME
Precision	For NUMERIC: maximum number of digits; for STRING: maximum length
Alias	Actual name of the column in the database table. This will be the name visible in the DB.
JSONPath Expression	JSONPath expression to extract the value from the source JSON file (e.g., $.persona.info.name)

Table Structure Management

Three buttons are located at the bottom right of the Tabular Structure tab:

Button	Action
Save	Save the defined structure
Import	Import an existing structure
Export	Export the defined structure

This feature allows you to save, export, or reuse mappings, avoiding the manual completion of fields and reducing the risk of errors.

Technical Note

• Visibility: Structures saved in the system are visible exclusively to the user who created them.
• Deep Dive: For a detailed description of all options and the operating logic, refer to the Table Structure page.

3. Configuration example

To create a users table with the following fields:

Type	Alias	JSONPath Expression
STRING	name	$.user.info.name
STRING	email	$.user.info.email
NUMBER	age	$.user.info.age
STRING	city	$.address.city
DATE	registration_date	$.registration.date
DATETIME	registration_datetime	$.registration.datetime

The result of this configuration will be the creation of a table in the database with the columns name, email, age, city, registration_date and registration_datetime (Alias) populated with the values extracted from the JSON (JSONPath Expression).

Aliases with special characters or uppercase letters

If you want to use uppercase letters, special characters, or spaces in column names, you must enclose the Alias in double quotes (e.g., "User Name").

Type	Alias	JSONPath Expression
STRING	"Name"	$.name
STRING	"Surname"	$.surname

Result in the Database

4. Handling nested JSON structures

The JSON to Table task is capable of handling JSON files containing nested structures, such as arrays or objects within other objects. Through the Path/Query Expression, you can extract entire nested blocks or single values, mapping them to table columns. The task can serialize a nested object into a single text column, preserving the original structure in JSON format.

Example: Complex nested structures

The task handles even much more complex structures with the same ease. Consider a JSON where departments contains an object, which in turn contains an array of other objects.

[
  {
    "name": "Luca",
    "surname": "Rossi",
    "departments": {
      "department": [
        {
          "details": {
            "name": "Marketing",
            "location": "Milan"
          }
        },
        {
          "details": {
            "name": "Sales",
            "location": "Rome"
          }
        }
      ]
    }
  },
  {
    "name": "Maria",
    "surname": "Bianchi",
    "departments": {
      "department": [
        {
          "details": {
            "name": "HR",
            "location": "Florence"
          }
        }
      ]
    }
  }
]

In this case too, the configuration in the "Table Structure" remains identical:

Type	Precision	Alias	JSONPath Expression
String	1000	name	$.name
String	1000	surname	$.surname
String	4000	departments	$.departments

The task will take the entire object associated with the departments key and serialize it into a single JSON string.

Result in the Database:

name	surname	departments
Luca	Rossi	{"department":[{"details":{"name":"Marketing","location":"Milan"}},{"details":{"name":"Sales","location":"Rome"}}]}
Maria	Bianchi	{"department":[{"details":{"name":"HR","location":"Florence"}}]}

Extracting sub-elements with JSONPath

If you wish to extract individual nested elements, such as department names, you can use a specific JSONPath Expression. For example:

Type	Precision	Alias	JSONPath Expression
String	1000	name	$.name
String	1000	surname	$.surname
String	1000	department_names	$.departments.department[*].details.name

With this configuration, the JSON to Table task selects all name values present in the department array and inserts them into the department_names column of the database, optionally separating them with a comma.

Result in the Database

name	surname	department_names
Luca	Rossi	Marketing, Sales
Maria	Bianchi	HR

This approach allows you to extract only the necessary data, without serialising the entire JSON block, making the table easier to query and use in other tasks or reports.

5. Output Parameters

Once the "JSON to Table" task has completed processing, it exposes a rich set of output parameters. These parameters not only provide information about the outcome of the operation but also make the processed data available in various formats for subsequent tasks.

• result: Provides the overall status of the task execution (e.g., "Success" or an error message).
• resultJson: Represents the result of the task execution in JSON format, including general information, configurations, and execution details.
• targetTable: Returns the name of the database table into which the data was inserted.
• rowCount: Indicates the total number of rows that were inserted into the target table.
• DataExportCsv: Returns the entire set of processed data, formatted as text in CSV (Comma-Separated Values) format.
• DataExportJson: Returns the entire set of processed data, formatted as a JSON string.
• Dataset: This parameter represents the name of the column in the table (e.g., name). It is the most important parameter for linking this task to an Iterator. To access the values of a specific column during an iteration, use the Dataset.ColumnName syntax.
• DataColumn: This parameter allows you to access the entire content of a column as a single entity (e.g., an array or list).
• DataColumn.ColumnName: Returns all values of the column "ColumnName". For example: "Luca, Maria, Giovanni, Elena"

6. Example resultJson parameter

{
  "run_info": {
    "status": "Completed",
    "run_result": "Success"
  },
  "output_parameters": {
    "exitStatus": "0"
  }
}