JSON (Javascript Object Notation) is a data interchange format, often used in web applications to transmit data. JSON has been widely adopted by web application developers because, compared to XML, it is easier for humans to read and write, and for machines it is easier to generate and parse. JSON documents can be stored and processed in Teradata Vantage.
- Ability to store JSON data in text and binary (BSON, UBJSON) storage formats.
- Methods, functions, and stored procedures that operate on the JSON data type, such as parsing and validation.
- Shredding functionality that allows you to extract values from JSON documents, and store the extracted data in relational format.
- Publishing functionality that allows you to publish the results of SQL queries in JSON format.
- Schema-less or dynamic schema with the ability to add a new attribute without changing the schema. Data with new attributes is immediately available for querying. Rows without the new column can be filtered out.
- Use existing join indexing structures on extracted portions of the JSON data.
- Apply advanced analytics to JSON data.
- Functionality to convert an ST_Geometry object into a GeoJSON value and a GeoJSON value into an ST_Geometry object.
- Allows JSON data of varying maximum lengths, and JSON data can be internally compressed.
- Collect statistics on extracted portions of the JSON data.
- Use standard SQL to query JSON data.
- JSONPath provides simple traversal and regular expressions with wildcards to filter and navigate complex JSON documents.
Client Support for JSON
The following table describes the support provided by the Teradata client products for the JSON data type.
| Client Product | JSON Support Provided |
|---|---|
| CLI | Full native DBS support. |
| ODBC |
The ODBC specification does not have a unique data type code for JSON. Therefore, the ODBC driver maps the JSON data type to SQL_LONGVARCHAR or SQL_WLONGVARCHAR, which are the ODBC CLOB data types. The metadata clearly differentiates between a Vantage CLOB data type mapped to SQL_LONGVARCHAR and a Vantage JSON data type mapped to SQL_LONGVARCHAR. The ODBC driver supports LOB Input, Output and InputOutput parameters. Therefore, it can load JSON data. Also the Catalog (Data Dictionary) functions support JSON. |
| JDBC |
|
| .NET Data Provider |
|
| Teradata Parallel Transporter (Teradata PT) | JSON columns are treated exactly like CLOB columns and subject to the same limitations. JSON columns cannot exceed 16 MB (16,776,192 LATIN characters or 8,388,096 UNICODE characters). Teradata PT accommodates the JSON keyword in object schema but internally converts it to CLOB. Import and export are both fully supported. |
| BTEQ |
The JSON keyword cannot be used in the USING data statement; therefore, JSON values must be referred to as either CLOB or VARCHAR. For VARCHAR, the JSON value cannot exceed 64 KB (64000 LATIN characters or 32000 UNICODE characters). Currently, BTEQ does not support deferred mode LOB transfer for server to client. Only non-deferred mode JSON transfer is supported for server to client, and the maximum size of an output row is limited to 64 KB. |
| Standalone Utilities | No support. |
- Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, B035-2417
- Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418
- ODBC Driver for Teradata® User Guide, B035-2509
- Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html
- Teradata® Parallel Transporter Reference, B035-2436
- Teradata® Parallel Transporter User Guide, B035-2445
- Basic Teradata® Query Reference, B035-2414
Terminology
- A JSON document is any string that conforms to the JSON format. See JSON String Syntax.
- A JSON document can be structured as an object or as an array.
- A JSON object consists of zero or more name:value pairs delimited by curly braces ( { } ).
- The value portion of a JSON object can be a single string, number, Boolean (true or false), null, array, or object.
- A JSON array is an ordered list of zero or more values delimited by square brackets ( [ ] ). Those values can be a single string, number, Boolean true or false, null, array, or object.
- JSON documents that are stored and used as Vantage JSON data types can be referred to as JSON instances or JSON type instances.
{
"name": "Product",
"properties": {
"id": {
"type": "number",
"description": "Product identifier",
"required": true
},
"name": {
"type": "string",
"description": "Name of the product",
"required": true
},
"price": {
"type": "number",
"minimum": 0,
"required": true
},
"tags": {
"type": "array",
"items": {
"type": "string"
}
},
"stock": {
"type": "object",
"properties": {
"warehouse": {
"type": "number"
},
"retail": {
"type": "number"
}
}
}
}
}