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. Integrator can parse and create practically any JSON document. You can use additional parameters to hint the JSON parser how to deal with the JSON nodes and arrays when reading and creating JSON documents. 

Use 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 Etlworks.

Creating format

To create a new fixed-length format, go to Connections, select Formats tab, click Add Format button, 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 the JSON format. If this field is not empty, 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, Integrator will try to parse it using one of the well-known date+time formats.
• Trim Strings - if this option is enabled, Integrator will trim leading and trailing white-spaces from the value.
• Treat 'null' as null - if this option is enabled, 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 dataset, for example, in different types of phone numbers. As a result, the nested dataset will be created with an additional name_type field. For example (showing the fields only): phone_type, number.
• Preprocessor - JavaScript code which modifies the contents of the source document. Read more how to use a Preprocessor.