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.
Not using GoAnywhere MFT yet? Start a free trial and test it out for 30 days.
What is 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:
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.
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.
Click the Add button and select 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.
Click the Add button and select 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”.
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.
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.
Click the Add button and select Column. On the Column element, specify values for the Index and Value 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:
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.
On the SQL task, specify values for the following attributes:
- Database Server - Select a pre-configured database server from the drop-down list.
Click the Add button to Add a Query to the SQL task.
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.
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.
The following image illustrates the 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.