Was this helpful?
JSON
JSON is a single mode connector that provides source and target connections to JSON data files.
JavaScript Object Notation (JSON) is an open-standard, text based, language-independent data format that is generally used to store and transmit data in web applications. It was derived from JavaScript, but most modern programming languages can generate and parse JSON data format. Since JSON is much easier to parse than XML hence it is promoted as a low-overhead alternative to XML.
A JSON file uses the .json extension and its official Internet media type is "application/json".
JSON is built on two data structures:
Objects: These are a set of name-value pairs and in most languages can be realized as an object, record, struct, dictionary, hash table, keyed list, or associative array.
Arrays: These are an ordered list of values and in most languages can be realized as an array, vector, list, or sequence.
Connectivity Requirements
For a JSON connector:
Source and target files can be stored on the local file system where Actian DataConnect is installed, and then accessed through shared storage locations.
Source and target files can be stored in an external system and accessed through HTTP or HTTPS requests.
Source and target connectors may be defined as a DJMessage type (djmessage:///<message name>)
Connector Parts
Connector parts are the fields you configure to connect with a data source or target. The fields that are available depend on the connector you select.
For a list of all parts for source connectors, see Specifying Source Connector, Parts, and Properties.
For a list of all parts for target connectors, see Specifying Target Connector, Parts, and Properties.
Connector Properties
You can specify the following source (S) and target (T) properties:
Property
S/T
Description
Batch Size
S
Number of records that the connector caches before processing them in a map.
Default value is 100.
Read Nulls
S
Determines whether null values are read. A null value is defined by the value of a name:value pair being explicitly set to Null.
TRUE (default): The connector reads null values in the source file.
FALSE: The null values in the source file are skipped. The null field is added to the schema, but it is skipped when the map is run.
Read Empty Tokens
S
Determines whether empty array ([]) and empty object ({}) values are read.
TRUE (default): Empty array ([]) and empty object ({}) values in the source file are read.
FALSE: Empty array ([]) and empty object ({}) values in the source file are skipped.
The schema is updated with a reference to a new record and the JSON INPUT field holds the {} and [] characters, respectively. The characters in the JSON INPUT field are not mapped to the target.
Note:  An empty string in a source that is supposed to be an object is treated as if it were {}.
Flush Frequency
T
(Optional) Number of records buffered in the memory before being written to the target connector.
Default value is 0 (all records are written at once).
When inserting many records, change the default to a higher value to improve performance.
Batch Response
T
(Optional) The file to which CRUD operation results are written. The format is one entry per record, indicating success or failure. If operation fails, information about cause of failure is returned.
HTTP Write Method
T
Determines the write method for an HTTP or HTTPS target. You can choose from POST (default) or PUT. This property is only used when the output file is a URL.
Supported Output Modes
JSON Connector supports the Replace output mode. For more information, see Target Output Modes.
Supported Data Types
JSON connector supports the following data types and these data types correspond to the fields in the target data table:
Boolean
Byte
Char
Date
Datetime
Decimal
Double
Float
Int
Long
Short
String
Time
Additional Information
Batch Response
A batch response file contains a group of entries for each connector used in a batch operation. Each action in the operation generates a batch response entry. These entries often provide detailed information, such as errors, that can be used for troubleshooting.
Example:
<BatchResponse:batchResult xmlns:BatchResponse="http://www.actian.com/BatchResponse">
<BatchResponse:results>
</BatchResponse:results>
<BatchResponse:componentInfo>
<BatchResponse:returnCode>0</BatchResponse:returnCode>
<BatchResponse:componentName>JSON-DMS</BatchResponse:componentName>
</BatchResponse:componentInfo>
</BatchResponse:batchResult>
Custom JSON
If the __._JSON_INPUT (JSON_INPUT field) is mapped on the target side, the field is a string field and the connector treats the mapped string as a Custom JSON.
The JSON_INPUT field provides a way to add a custom output structure to a target record. When discovering a JSON schema, the JSON_INPUT field is automatically created as the first field in a record. If the JSON_INPUT field is empty in the target then it will be ignored, however, if it has a value, then the processing of subsequent fields in the record is ignored. Therefore, if used, the JSON_INPUT field must be the first field listed for the record type.
WARNING!  The connector may output invalid JSON if you do not validate the JSON when mapping the Custom JSON field. This is because the connector does not validate the data in the JSON_INPUT field.
There are some considerations you need to take into account when using the JSON_INPUT field:
If the JSON_INPUT field is mapped on a record, all child records of this record will be skipped, even if they are mapped. The remaining fields on the record will be skipped as well (even if they are mapped).
Since the JSON_INPUT field is a string, you must escape any string contained in the custom JSON data field, as shown in the following example:
User input to JSON_INPUT field:"{""Field"":""Value"", ""Field2"":12}""
Connector output for JSON_INPUT:{"Field":"Value", "Field2":12}
In this example, Field, Value, and Field2 are strings. The value of 12 is a number.
If a schema contains an empty array [] or empty object {}, __._JSON_INPUT can be set to [] or {} respectively, to correctly output the record.
Another use would be to add content based on an EZscript expression. For example, creating a name:value pair with the processing date:
="""theDate"" : """ & Date() & """"
In this example, field names and values are in double quotes to escape strings.
Last modified date: 07/26/2024