JSON Format

When to use this Format

JSON is a data exchange format widely used as a payload and response in REST-based web services. The Etlworks Integrator can parse and create practically any JSON document. You can use additional parameters to hint the JSON parser on dealing with the JSON nodes and arrays when reading and creating JSON documents. 

Use the JSON Format when configuring a source-to-destination transformation that reads or writes JSON documents.

Read how to work with nested JSON and XML files in the Etlworks Integrator.

Create Format

To create a new fixed-length Format, go to Connections, select the Formats tab, click Add Format, type in json in the search field, and select JSON. Note that it is not the same as JSON Data Set. 

Below are the available parameters:

• First Object Type: the type of the first object in the JSON. This parameter is used as a hint when reading JSON and as an instruction when writing JSON. Options:

- array: [].

- named object: {"root": []}.

- object: {"field":"value"}.

- variable: either array or object, depending on the number of records in the dataset.

• Root Object Name: the name of the first object when named object or variable is used as the First Object Type.
• Nested Object Type: the type of all nested objects in the JSON. This parameter is used as a hint when reading JSON and as an instruction when writing JSON. Options:

- array: [].

- object: {"field":"value"}.

- variable: either array or object, depending on the number of records in the dataset.

• Template: this is a template in JSON Format. If this field is not empty, the Etlworks Integrator will use it to populate column names and data types. Template is an optional field. For example: {"firstName":"string","lastName":"string","age":23}.
• Column names compatible with SQL: convert column names to SQL-compatible column names by removing all characters, except alphanumeric and spaces.
• All fields are strings: if this option is enabled (it is disabled by default), the system will create all fields with a string data type.
• Date and Time Format: a Format for timestamps (date+time).
• Date Format: a Format for date (date only, no time).
• Time Format: a Format for time (time only, no date).
• Parse Dates: if this option is enabled, and the date or time value is not recognized as one of the Formats defined above, the Etlworks Integrator will try to parse it using one of the well-known date+time Formats.
• Trim Strings: if this option is enabled, the Etlworks Integrator will trim leading and trailing white spaces from the value.
• Treat 'null' as null: if this option is enabled, the Etlworks Integrator will treat string values equal to null as actual nulls (no value).
• Enable key-value: enable this option to handle the special case when there is a single field-nested data set, for example, in different types of phone numbers. As a result, the nested data set will be created with an additional name_type field. For example (showing the fields only): phone_type, number.
• Preprocessor: JavaScript code that modifies the contents of the source document. Read more about how to use a Preprocessor.