»  »  »  » 

How to Read JSON Data and Insert it into a Database

In this tutorial, you will learn how to read data from JSON files and load that data into RowSet variables using the Read JSON task. Those RowSets can then be used to insert the data into a database or translate the data to another file type such as XML, Excel, fixed-width, or CSV.

JSON

JSON is short for JavaScript Object Notation. JSON is represented in a logical, organized, and easy-to-access manner. JSON files can contain multiple levels of objects, arrays, and various field data that can be parsed by GoAnywhere MFT. When GoAnywhere reads a JSON file, the contents of the file are stored in one or more RowSet variables, which contain a list of records. For example, you could have a JSON file that contains customer information along with a list of the products the customer ordered. The customer information can be parsed into one RowSet and the list of products into another RowSet. Those RowSet variables can then be inserted into two separate database tables or written to another format, like CSV, Excel, or XML.

The following image represents a simple JSON input file and will be referred to throughout this tutorial:

Example JSON File

Create a New Project

To begin, create a new Project following the Getting Started with Projects tutorial.

Project Designer – The Read JSON Task

From within the Project Designer page, expand the Data Translation folder in the Component Library, and then drag the Read JSON task to the Project Outline.

Read JSON Task

On the Read JSON task, specify values for the File attribute:

  • Input File – The file path and file name of the JSON file to read.
  • Input File Sets – Optionally, you can define a File Set that contains a list of files to read.
Read JSON Task Attributes

Click the Add button and select RowSet.

Add New RowSet

On the RowSet element, specify a value for the Variable Name attribute:

  • Variable Name – The name of the variable which will contain the parsed data. The RowSet variable will be used to store order information.
RowSet Element Variable Name

Click the Add button and select Column.

Add Column

On the Column element, specify values for the following attributes:

  • Index – The index of the column in the output RowSet. The first column starts with index 1.
  • Value – The path to the field from which this column should draw data. To retrieve data from a field, the path should be defined like “/Object/Array/Field”. For example, to retrieve the order number from the example above, use “/orders/orderno”. To retrieve the customer ID, use “/orders/customer/custid”.
Column Element Attributes

Repeat the previous two steps to add additional columns for the remaining order information.

Create a second RowSet variable to store the customer information from the JSON file.

Example JSON File Array

Select the Read JSON task from the Project Outline and then click the Add button and select Add RowSet to add another RowSet variable. On the RowSet element, specify a value for the Variable Name attribute.

RowSet Element Variable Name

Click the Add button and select Column. On the Column element, specify values for the Index and Value attributes.

Column Element Attributes

Click the Add button in the sub-menu and select Add Same. Repeat the last two steps to add additional columns and elements from the JSON file.

The following image illustrates the Project Outline for the Read JSON task, which contains two RowSet variables for columns and elements read from the JSON file:

Read JSON Project Outline

Example Output

To translate the data gathered from the Read JSON task into a database, expand the Database folder in the Component Library, and then drag the SQL task to the Project Outline.

SQL Task

On the SQL task, specify values for the following attributes:

  • Database Server - Select a pre-configured database server from the drop-down list.
SQL Task Attributes

Click the Add button to Add a Query to the SQL task.

Add Query

Type in your SQL Insert Statement on the page. In this example, the table name is ‘orders’, and four placeholder '?' are used for the Indexes referenced in the 'orders' RowSet.

SQL Statement

Specify the RowSet variable name '${orders}' into the Input RowSet Variable field. The specified SQL Statement will be executed once per each row in this RowSet.

To insert the data contained in the ${customer} RowSet variable into a database, add a second query by clicking the Add button and Add Same. Because the ${customer} RowSet variable contains seven indexes, seven placeholder ‘?’ are used.

SQL Statement

The following image illustrates the final Project Outline.

Final Project Outline

When executed, the project will read the data from the JSON file and load that data into RowSet variables using the Read JSON task. Those RowSets are then used to insert the data into two separate database tables using the SQL task.

Still have questions? Browse our forum, post a question, or live chat with a technician now!