How to Read JSON Data and Insert Into a Database

GoAnywhere MFT can connect to RESTful web services for transferring or manipulating JSON and other data formats. 

In this tutorial, you will learn how to use GoAnywhere Managed File Transfer 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 with no coding required.

Text

Not using GoAnywhere MFT yet? Start a free trial and test it out for 30 days.

START FREE TRIAL 

What is JSON?

Text

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:

 

Image
Example JSON File
Example JSON File

Create a New Project

Text

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

Project Designer – The Read JSON Task

Text

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.

 

Image
Read JSON Task
Read JSON Task
Text

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.

 

Image
Read JSON Task Attributes
Read JSON Task Attributes
Text

Click the Add button and select RowSet.

 

Image
Add New RowSet
Add New RowSet
Text

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.

 

Image
RowSet Element Variable Name
RowSet Element Variable Name
Text

Click the Add button and select Column.

Image
All Column
Add Column
Text

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”.

 

Image
Column Element Attributes
Column Element Attributes

 

Text

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.

 

Image
Example JSON File Array
Example JSON File Array
Text

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.

 

Image
RowSet Element Variable Name
RowSet Element Variable Name
Text

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

 

Image
Column Element Attributes
Column Element Attributes
Text

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:

Image
Read JSON Project Outline
Read JSON Project Outline

Example Output

Text

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.

 

Image
SQL task
SQL Task
Text

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

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

 

Image
SQL Task Attributes
SQL Task Attributes
Text

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

 

Image
Add Query
Add Query
Text

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.

 

Image
SQL Statement
SQL Statement
Text

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.

 

Image
SQL Statement
SQL Statement
Text

The following image illustrates the final Project Outline.

 

Image
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?

Are you trying to convert JSON data to other data formats? Start a free trial of GoAnywhere MFT and see how our automation tools can save you time and money.

Start a Free Trial
Aug
30
Tuesday
Aug 30, 2022
8:30 - 9:30am