How to Query a Database and Write the Data to JSON

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

Posted on October 4, 2022
Text

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.

 

Image
Write JSON Illustration
Write JSON Illustration
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 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.

Getting Started: Create a New Project

Text

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

Project Designer – The SQL Task

Text

Then, from within the Project Designer, 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, select the Database Server resource from the drop-down list.

 

Image
SQL Task
SQL Task
Text

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.

 

Image
SQL Statement for the Orders Table
SQL Statement for the Orders Table
Text

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.

 

Image
Contents of the 'orders' RowSet Variable
Contents of the 'orders' RowSet Variable
Text

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

 

Image
SQL Statement for the Customers Table
SQL Statement for the Customers Table
Text

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

 

Image
Contents of the 'customers' RowSet Variable
Contents of the 'customers' RowSet Variable

Project Designer – The Write JSON Task

Text

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.

 

Image
Write JSON Task
Write JSON Task
Text

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.

 

Image
Write JSON Task Attributes
Write JSON Task Attributes
Text

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

 

Image
Add New Object
Add New Object
Text

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:

 

Image
Object Element Attributes
Object Element Attributes

 

Text

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.

 

Image
Array Element Attributes
Array Element Attributes
Text

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.

 

Image
Object Element Attributes
Object Element Attributes
Text

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.

 

Image
Field Element Attributes
Field Element Attributes
Text

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:

 

Image
Write JSON Project Outline
Write JSON Project Outline
Text

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.

 

Image
Add Array
Add Array
Text

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.

 

Image
Object Condition Statement
Object Condition Statement
Text

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

 

Image
Write JSON Project Outline
Write JSON Project Outline
Text

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

 

Image
JSON File Output

Still have questions?

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.
Start a Free Trial