Function Description of JSON Parsing

Version

Application Scenario

When you fetch data through APIs or read data from non-relational or relational databases, the retrieved data may contain JSON-formatted content.

Furthermore, in many cases, the JSON-formatted data is not standardized and cannot be directly mapped to a two-dimensional table for use.

The JSON Parsing operator can parse JSON data and output it in a row-column format.

Function Description

The JSON Parsing operator receives the JSON data output by the upstream source, allows for custom parsing into the required fields, and outputs the result to downstream nodes.

Example:

The JSON data in a certain column is as follows:

[ { "category": "reference", "author": "Nigel Rees", "title": "Sayings of the Century", "price": 8.95 }, { "category": "fiction", "author": "Evelyn Waugh", "title": "Sword of Honour", "price": 12.99 }, { "category": "fiction", "author": "Herman Melville", "title": "Moby Dick", "price": 8.99, "isbn": "0-553-21311-3" }, { "category": "fiction", "author": "J.R.R. Tolkien", "title": "The Lord of the Rings", "price": 22.99, "isbn": "0-395-19323-5" }]

After parsing with the JSON Parsing operator, it becomes:

Note:

During JSON parsing, extracting and expanding fields from different hierarchical levels simultaneously is not supported. Instead, you can use multiple JSON Parsing operators to parse fields at different levels. For details, see the section "Parsing Fields with Duplicate Names Using Multiple JSON Parsing Operators" of this document.

The configuration page of the JSON Parsing operator is shown in the following figure.

Selecting the Source Field

Select the field to be parsed from the drop-down list of Source Field.

Starting from FineDataLink V4.1.6.2, JSON Parsing can parse multiple fields simultaneously, reducing the processing cost for multiple complex fields from Jiandaoyun Input.

After selecting multiple fields in Source Field, you can select them simultaneously in Select JSON Node for parsing, as shown in the following figure.

If the upstream is an API Input operator, the default selection in Source Field is generally responseBody, as shown in the following figure.

If the upstream is a DB Table Input or other input operators, you can choose the JSON source field that needs to be parsed, as shown in the following figure.

Keeping Upstream Output Fields

Starting from FineDataLink V4.2.10.1, the checkbox option Keep All Output Fields After Parsing is renamed Keep Upstream Output Field, and you can select specific upstream fields to retain.

• If this checkbox is unticked, only data of the selected source fields will be kept.

• If this checkbox is ticked, data of all upstream output fields and data of the selected source field after parsing will be retained.

• If you tick this checkbox and select partial fields, data of the selected upstream output fields and data of the selected source field after parsing will be retained.

• The page is shown in the following figure.

Parsing JSON Data

Select a JSON node for parsing:

This operator generates JSONPath expressions from the root nodes to form complete absolute paths by default. The paths are editable.

Note:

1. You are advised to select the root node for JSON parsing. This ensures parsing based on an absolute path. If you select specific fields using a relative path, the data parsing results may be incorrect.

2. You can use multiple JSON Parsing operators to parse nested JSON data.

You can perform operations such as renaming and adding fields, as shown in the following figure.

Starting from FineDataLink V4.2.10.1, you can set the data types of fields to read data in accordance with specified types. The available types include varchar, int, long, float, double, timestamp, boolean, and date.

Parsing Full-Field Data

You can modify the selectable content by pasting a JSON text after clicking Select JSON Node, preventing situations where, when multiple lines of JSON text flow from upstream, properties from other JSON lines might be unselectable due to the first line having fewer properties.

For example, if the selectable content does not include the required JSON properties, you can paste the JSON text that contains the required properties, as shown in the following figure. 

You can then select the fields you want to parse in nodes. 

Parsing Fields at Different Hierarchical Levels

For example:

Starting from FineDataLink V4.1.6.2, the simultaneous parsing of both the array and non-array parts of a single JSON source field is supported. 

For example, if you want to parse the class and name fields located at different hierarchical levels, you can directly select the corresponding fields to parse both the array and non-array parts of a single data source simultaneously, as shown in the following figure.

The JSON parsing operator in FineDataLink of versions earlier than V4.1.6.2 does not support the simultaneous parsing of both the array and non-array parts of a single JSON source field.

When you use the JSON Parsing operator to parse subform data, attempting to parse fields, such as level and name, located at different hierarchical levels (as shown in the following figure) would result in an error.

The line count in the JSON parsing result does not match. 

This is because extracting and expanding fields at different hierarchical levels simultaneously was not supported.

Solution:

You can perform parsing in two steps: first, parse fields at the same level (level and department), and then parse the nested field (the name field is under the department field).

Parsing JSON Data with Duplicate Field Names Across Different Levels

Starting from FineDataLink V4.1.6.2, when JSON data contains duplicate field names across different levels, the duplicate field name will be suffixed automatically, as shown in the following figure.

Parsing Fields with Duplicate Names Using Multiple JSON Parsing Operators

For example:

Note:

This issue does not occur in FineDataLink V4.1.6.2 and later versions.

Assume that you use two JSON Parsing operators to parse two different fields, but select the same field name (name) in Select JSON Node for both operators, as shown in the following figure.

In this scenario, if multiple JSON Parsing operators are used, the field names after parsing cannot be the same. Therefore, you need to rename the duplicate fields. Otherwise, the subsequent fields with the same name will overwrite the previous ones, as shown in the following figure.

Typical examples of the JSON Parsing operator are as follows:

JSON Parsing Operator Example

Parsing and Associating Multiple JSON Array Fields

Filtering out Invalid Data Before JSON Parsing