JSON
JSON connectors facilitate working with JSON, a popular semi-structured data format used extensively in web APIs and applications. They enable parsing, querying, and transformation of JSON data to support modern data exchange needs.
important
Using this connector requires an add-on license, included in the custom connectors package.
Prerequisites
Before connecting, ensure the following prerequisites are met:
- Verify that the storage or folder location is accessible from the cluster.
- Gather valid user credentials and ensure the user has appropriate permissions to read the files.
- Ensure at least one file is present in the folder.
Connecting to Amazon S3
AWS Custom Credential Authentication
Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | JSON Custom JDBC |
| Container * | The name of the S3 bucket that contains the JSON files. | acme-prod-bucket |
| Folder Path * | The directory path within the bucket that points to the folder containing the files. | /data/prod-files/ |
| Region * | The AWS region where the S3 bucket is located. | us-east-1 |
| Type * | The connection type – either System Connection or User Connection. Refer Connections for more details. | System |
| Access Key * | The key used to authenticate the connection to the storage account. | fnsdt8765dfyjm67i5mnvfhcn56858 |
| Secret Key * | The secret key used to authenticate the connection to the storage account. | wJalrXnMI/K7MDEN-G/bPxRfiEKEY |
| Session Token * | Temporary token used for session-based authentication. | FQXdzEJr/wEaDKr2EjTokenjKEN== |
warning
The Access and Secret keys must be updated whenever the session token expires.
AWS EC2 Role Authentication
Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | JSON Custom JDBC |
| Container * | The name of the S3 bucket that contains the JSON files. | acme-prod-bucket |
| Folder Path * | The directory path within the bucket that points to the folder containing the files. | /data/prod-files/ |
| Region * | The AWS region where the S3 bucket is located. | us-east-1 |
| Type * | For EC2 role authentication, the connection type must be a System Connection. Refer Connections for more details. | System |
AWS IAM Authentication
Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | JSON Custom JDBC |
| Container * | The name of the S3 bucket that contains the JSON files. | acme-prod-bucket |
| Folder Path * | The directory path within the bucket that points to the folder containing the files. | /data/dev-files/ |
| Region * | The AWS region where the S3 bucket is located. | us-east-1 |
| Type * | The connection type – either System Connection or User Connection. Refer Connections for more details. | System |
| Access Key * | The key used to authenticate the connection to the storage account. | eargaerh529789sf23fsd |
| Secret Key * | The secret key used to authenticate the connection to the storage account. | wEU3mK30P4EjEOKEN== |
Connecting to Azure Blob Storage
Azure Access Key Authentication
Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | JSON Custom JDBC |
| Container * | Name of the storage container where the JSON files are stored. | acme-prod-bucket |
| Folder Path * | The directory path within the container that points to the folder containing the files. | /data/prod-files/ |
| Type * | The connection type – either System Connection or User Connection. Refer Connections for more details. | System |
| Account Name * | The storage account name used for authentication. | prodstorageaccount |
| Access Key * | The key used to authenticate the connection to the storage account. | eargaerh789sf23fsd |
Azure Shared Signature Authentication
Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | JSON Custom JDBC |
| Container * | Name of the storage container where the JSON files are stored. | acme-prod-bucket |
| Folder Path * | The directory path within the container that points to the folder containing the files. | /data/prod-files/ |
| Type * | The connection type – either System Connection or User Connection. Refer Connections for more details. | System |
| Security Token * | The shared access signature (SAS) token used to authenticate access to Azure Blob Storage. | sv=2:59http&sig=signature |
| Account Name * | The name of the Azure storage account hosting the Blob Storage container. | prodstorageaccount |
Connecting to FTP
Anonymous Authentication
Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | JSON Custom JDBC |
| Host * | IP address or hostname of the FTP server. | ftp.acme.com or 193.164.68.41 |
| Port * | The port on which the server listens. Default: 21 for FTP. | 21 |
| Folder Path * | The directory path within the server leading to the folder containing the files. | /data/acme-files/ |
| Type * | For anonymous authentication, the connection type must be a System Connection. Refer Connections for more details. | System |
Username and Password Authentication
Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | JSON Custom JDBC |
| Host * | IP address or hostname of the FTP server. | ftp.acme.com or 193.164.68.41 |
| Port * | The port on which the server listens. Default: 21 for FTP. | 21 |
| Folder Path * | The directory path within the server leading to the folder containing the files. | /data/acme-files/ |
| Type * | The connection type – either System Connection or User Connection. Refer Connections for more details. | System |
| Username * | FTP login username with necessary privileges. | ftp_user |
| Password * | Password associated with the specified username. | Str0ngP@ssw0rd2025! |
Connecting to Google Cloud Storage
Service Account Authentication
Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | JSON Custom JDBC |
| Project ID * | The unique identifier of the Google Cloud project where the storage bucket resides. | acme-gcp-project-5726 |
| Container * | Name of the storage bucket where the JSON files are stored. | prod-data-container |
| Folder Path * | The directory path within the bucket that points to the folder containing the files. | data/prod-files/ |
| Type * | For service account authentication, the connection type must be a System Connection. Refer Connections for more details. | System |
Connecting to Local Storage
Users can read files from local storage, which may refer to a server directory for uploaded files or a network-mounted directory accessible to the cluster. Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | JSON Custom JDBC |
| Folder Path * | The directory path leading to the folder containing the files. | /data/prod-files/ |
warning
This connector does not support authentication for accessing files from local storage.
Connecting to SFTP
Username and Password Authentication
Use one or more properties from the table below to create a valid connection. Properties marked with an asterisk (*) are required.
| Name | Description | Example Values |
|---|---|---|
| Connection Name * | Name that uniquely identifies the connection. | Product_file_conn |
| Driver * | Driver used to establish the connection. By default, one driver is available. | JSON Custom JDBC |
| Host * | IP address or hostname of the SFTP server. | sftp.acme.com or 193.164.68.41 |
| Port * | The port on which the server listens. Default: 22 for SFTP. | 22 |
| Folder Path * | The directory path within the server leading to the folder containing JSON files. | acme/data/files/ |
| Type * | The connection type – either System Connection or User Connection. Refer Connections for more details. | System |
| Username * | SFTP login username with necessary privileges. | reporting_user |
| Password * | Password associated with the specified username. | Rep0rt!ng2025$ecure |
Custom Properties
Following optional connection properties can be configured based on user requirements:
| Property | Default Value | Possible Values | Description |
|---|---|---|---|
| JSONFormat | JSON | JSON, LDJSON, JSONROWS | Specifies the structure or formatting style of JSON data. |
| DataModel | FlattenedDocuments | Document, FlattenedDocuments, Relational | Specifies the data model to use when parsing JSON documents and generating the database metadata. |
| Charset | UTF-8 | String | Specifies the session character set for encoding and decoding character data transferred to and from the JSON file. |
| FlattenArrays | String value | Any string value | Flattens the elements of nested arrays into columns of their own. |
| FlattenObjects | true | true, false | If true, flattens object properties into columns of their own; otherwise, nested objects are returned as JSON strings. |
| RowScanDepth | 100 | Integer value | The maximum number of rows to scan to look for the columns available in a table. |
| Timeout | 60 | Integer value | Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error. |
| TypeDetectionScheme | None | None, RowScan | Determines how the provider detects the data types of columns. |
| ConnectOnOpen | true | true, false | Specifies whether the provider establishes a connection to JSON immediately upon opening the connection. |
| MaxRows | -1 | Numeric value | Specifies the maximum rows returned for queries without aggregation or GROUP BY. |
| BatchSize | 0 | Numeric value | Specifies the maximum number of rows included in each batch operation. Set to 0 to submit the entire batch as a single request. |
| IncludeFiles | JSON | JSON, TXT | Comma-separated list of file extensions to include into the set of files modeled as tables. |
| Readonly | false | true, false | Toggles read-only access to JSON from the provider. |
Supported Datatypes
The following data types are supported:
- INTEGER
- FLOAT
- DECIMAL
- VARCHAR
- STRING
- BOOLEAN
- DATE
- TIME
- TIMESTAMP
Unsupported Datatypes
The following data types are not supported:
- BLOB
- XML
Related Articles
The following resources provide additional guidance on working with JSON in iceDQ.
| Description | Link |
|---|---|
| Working with JSON | View Article |