Create database json

GoAnywhere MFT can connect to RESTful web services for transferring or manipulating JSON and other data formats. Learn more about JSON and RESTful Web Services.

In this tutorial, you will learn how to query two database tables, join the data using a conditional expression, and write the data to a JSON file. This file could then be imported into other systems or the data can be posted to a Web Service call via REST.

JSON is short for JavaScript Object Notation. JSON data is represented in a logical, organized, and easy-to-access manner. JSON can contain multiple levels of objects, arrays, and various field data that can be parsed by GoAnywhere MFT.

Create a New Project

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

Project Designer The SQL Task

Then, from within the Project Designer, expand the Database folder in the Component Library, and then drag the SQL task to the Project Outline.

On the SQL task, select the Database Server resource from the drop-down list.

Click the Add button to Add a Query to the SQL task. The orders database table will be queried first.

Type in your SQL select statement in the SQL Statement field. Alternatively, you can use the SQL Wizard for quickly building the Select statement. With the SQL wizard, you can choose schemas (libraries), tables (physical files), columns (fields), column headings, "where" clauses and "order by" criteria. To access the SQL Wizard for building a SELECT statement, click the browse icon on the right side of the SQL Statement field. See the SQL Wizard tutorial for more information.

The following table illustrates data that is contained in the RowSet variable named orders. The variable along with the index notation, such as ${orders[1]}, is used to populate the JSON field data in subsequent steps.

Returning to the SQL Task, add a second query for the customers database table.

The following table illustrates data that is contained in the RowSet variable named customers.

Project Designer The Write JSON task

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

On the Write JSON task, specify the following fields:

• Output File - The file path and file name of the JSON file to write.
• Tidy Output When set to true, the JSON file will be formatted with standard indention between objects, arrays, and fields.

In the Write JSON task window, click the Add button in the sub-menu, and then select Object.

On the Object element, specify a value for the following attributes:

• Name - Specify the name for this object. If this is not a child of an object, the name is used as a reference within the Project Outline. If this object is defined as the child of another object, the Name is used as a field name in the JSON file. In this example, this object is not the child of another object, so the value in the Name field is only used as a reference in the Project Outline and is not used in the JSON file. An open bracket "{" is specified in the element to make it easier to identify the beginning of this Object in the Project Outline. Closing brackets are automatically inserted for the user in the output. Here is the final JSON file output for reference:

In the Object element click the Add button in the sub-menu, and then select Array. The Array element iterates through each row in a RowSet variable. In JSON terms, an Array will add a square bracket [ ] structure to your data.

On the Array element, specify the Input RowSet Variable value:

• Name - This array is the child of an object, and is used as a field label within the JSON file. The first array in our JSON output will contain data from the orders database table, so we will name this array orders.
• Input RowSet Variable - The name of a variable which contains the RowSet data to write to a file. For example, from the SQL task we used orders as the output variable so ${orders} will be used as input here.

This array will contain multiple objects. In the Write Array element, click the Add button in the sub-menu, and then select Object.

On the Object element, specify a Name. In this example, an open bracket "{" is specified in the element to make it easier to identify the beginning of this Object in the Project Outline.

In the Object element, click the Add button in the sub-menu, and then select Field.

On the Field element, specify the following fields:

• Name - Specify the field name as it will appear in the output JSON file.
• Value - Specify the value of this field. This may be expressed by a combination of constant values and RowSet column references. For example ${orders[1]}, where order is the name of the RowSet variable and [1] is the column index. Only RowSets which are in use by a parent Array may be used. Refer to the SQL statement in the Query task for the correct column order.

As needed, add additional fields, from the Add link in the sub-menu by selecting the Add Same option. Follow the same process above, specifying the field name that will appear in the output file along with the appropriate column index of the RowSet variable.

Click the Save button when finished. The following image illustrates the Project Outline so far:

The process for building the second array with data from the ${customers} RowSet variable is the same. Select the Object element of the orders array. Click the Add button and select Array. Name this array 'customers' and specify the ${customers} RowSet variable in Input RowSet Variable field.

Add the Object that will contain the customer fields. Select the customers array, click the Add button, and select Object. To join data from both tables using the 'custid' as a qualifier, add a conditional expression to the new Object element.

Follow the same process outlined previously when adding fields to the customers array with data from the ${customers} RowSet variable.

The following image illustrates the JSON file created from the Write JSON task:

Are you trying to convert Database, Flat File, Excel, CSV, or XML data into the JSON format? Start a free trial of GoAnywhere MFT and see how our automation tools can save you time and money.

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