JSON Generation

Version

Application Scenario

To write API data into a specified interface using the API Output operator, you may need to transform database data or other forms of data into JSON data to facilitate data interaction.

Function Description

You can use the JSON Generation operator of FineDataLink to generate JSON data.

JSON Field

Field name: The system generates a JSON object by assembling the selected incoming fields. The JSON field name defaults to JSON_DATA, which is editable.

Merge JSON Fields by Group Field: You can group (and merge) values of the selected field by group field value.

The following figure describes the group field.

JSON Setting

Format Setting

You can add and delete key-value pairs in the automatically created object and set values to nested subobjects or subarrays (one nesting level). Multiple JSON objects can be generated according to the configuration.

Configuration items with options include the key, the value type, the value, and the value format.

• Key: You can select a key and define the key name.

• Value type and value: You can set the value type to Field, Subobject, and Subarray.

FineDataLink of V4.2.5.2 and later versions support custom fixed values, as shown in the following figure.

• Value format: Options include String, Numeric, and Boolean. By default, it inherits the data type of the selected field. (If the field value is a number, the generated value will remain a number without double quotes. If the field value is true or false, the generated value will be a logical value true or false without double quotes. In other cases, the generated value will be a string.) If you select String, the generated value will be a string with double quotes. At runtime, field data in the actual data flow will be forcibly converted to values in the specified formats. An error will be reported if the conversion fails. For subarrays, you can only add one row of values at the front end, where the value type can be set to Field or Subobject only. 

Quick Generation and Emptying

FineDataLink provides Quick Generation and Empty buttons.

Field-Based Automatic Generation: An object is automatically generated based on incoming fields in the data flow, where a key-value pair is created for each field in the object. The key is the field name, the value is the field value, and no nesting is included.

JSON-Template-Based Generation: The JSON object is generated according to the JSON template you paste, as shown in the following figure.

Empty: You can click this button to empty all configuration in JSON Setting.

Generating an Array as a Whole

You can tick Generate Array as a Whole to convert the configured JSON object as a whole into an array ([]), as shown in the following figure.

• The JSON nesting depth in JSON Setting could not exceed three levels.

Note:

This restriction has been lifted since FineDataLink V4.2.5.2.

• Only one subarray ([]) is allowed at the same nesting level in JSON Setting. 

• A field can only be selected once in the JSON Generation operator. The selected fields are grayed out. 

Note:

This restriction has been lifted since FineDataLink V4.2.5.2.

• If the field value is a number, the generated value will remain a number without double quotes. If the field value is true or false, the generated value will be a logical value true or false without double quotes. In other cases, the generated value will be a string.

• If you select String, the generated value will be a string enclosed in double quotation marks ("").

• For subarrays, you can only add one row of values at the front end, where the value type can be set to Field or Subobject only. 

• Same names are not allowed for keys at the same level but are allowed for keys across levels.

You can download the sample data: dw_comment.xlsx

Transform user data in a table format into JSON data and then use the API Output operator to write it into an API of the business system to trigger business processes, thus achieving data interaction.

This document details how to use the JSON Generation operator.

Data Fetching

Drag a Data Transformation node to the design page and click it. Drag a DB Table Input operator to the configuration page and use the SQL statement shown in the following figure to fetch the data that needs to be output to an API.

Click Data Preview, as shown in the following figure.

JSON Setting

The data to be written into the API should be JSON data, so you need to transform the original data in the database table format into JSON data.

The following is an example of JSON data:

{
	"fields": {
		"summary": "The document is excellent.",
		"components": {
			"name": [
				"FDL"
			]
		},
		"customfield_14904": "https://help.fanruan.com/finedatalink-en/doc-view-51.html",
		"description": "FDL Deployment Environment Preparation"
	}
}

Drag a JSON Generation operator to the design page, and configure Output Field and JSON Setting.

Click the + button in JSON Setting, as shown in the following figure.

Enter the first key fields and configure its subobject, as shown in the following figure.

Enter the key summary in the subobject and select the comment field as its value, as shown in the following figure.

You can modify the value type to String, Value, and Boolean, as shown in the following figure.

The final effect is shown in the following figure.

If you click Automatic Generation, an object without nesting is automatically generated for fields in the data flow entering the JSON Generation operator, where each field is represented as a key-value pair with the field name as the key and the field value as the value, as shown in the following figure.

Data Preview

Click Data Preview to view the generated JSON data.

Click a piece of JSON data, and you can view and copy the detailed content on the right side, as shown in the following figure.

For details about subsequent operations, see API Output.