JSON Methods, Functions, External Stored Procedures, and Table Operators - Teradata Vantage NewSQL Engine - 16.20

Teradata Vantage™ JSON Data Type

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K

The following methods, functions, procedures, and operators allow you to perform common operations on the JSON type to access or manipulate JSON data.

Methods

AsBSON
Returns the BSON representation of the specified JSON instance.
AsJSONText
Returns the text representation of the specified JSON instance.
Combine
Takes two JSON documents and combines them into a JSON document structured as an array or a JSON document structured as an object.
ExistValue
Allows you to specify a name or path in JSONPath syntax to determine if that name or path exists in the JSON document.
JSONExtract
Extracts data from a JSON instance. The desired data is specified in a JSONPath expression. The result is a JSON array composed of the values found, or NULL if there are no matches.
JSONExtractValue
Allows you to retrieve the text representation of the value of an entity in a JSON instance, specified using JSONPath syntax.
JSONExtractValue extracts a single scalar value or a JSON null. The returned value is a VARCHAR with a default length of 4K, but this can be increased up to 32000 characters (not bytes) using the DBS Control JSON_AttributeSize field.
JSONExtractLargeValue
Functions the same as JSONExtractValue, except for the return size and type. For LOB-based JSON objects, this method returns a CLOB of 16776192 characters for CHARACTER SET LATIN or 8388096 characters for CHARACTER SET UNICODE.
KeyCount
Returns the number of keys in a JSON document.
Metadata
Returns metadata about a JSON document such as the number of values that are Objects, Arrays, Strings, or Numbers.
StorageSize
Returns the number of bytes needed to store the input JSON data in the specified storage format.

Functions

ARRAY_TO_JSON
Allows any Teradata ARRAY type to be converted to a JSON type composed of an array.
BSON_CHECK
Checks a string for valid BSON syntax and provides an informative error message about the cause of the syntax failure if the string is invalid.
DataSize
Returns the data length in bytes of a JSON instance.
GeoJSONFromGeom
Converts an ST_Geometry object into a JSON document that conforms to the GeoJSON standards.
GeomFromGeoJSON
Converts a JSON document that conforms to the GeoJSON standards into an ST_Geometry object.
JSON_AGG
This aggregate function takes a variable number of input parameters and packages them into a JSON document.
JSON_COMPOSE
This scalar function takes a variable number of input parameters and packages them into a JSON document. This function provides a complex composition of a JSON document when used in conjunction with the JSON_AGG function.
JSON_CHECK
Checks a string for valid JSON syntax and provides an informative error message about the cause of the syntax failure if the string is invalid.
JSONGETVALUE
Extracts a value from a JSON object as a specific type.
JSONMETADATA
An aggregate function that returns metadata about a set of JSON documents.
NVP2JSON
Converts a string of name-value pairs to a JSON object.

Table Operators

JSON_KEYS
Parses a JSON instance, from either CHAR or VARCHAR input and returns a list of key names.
JSON_PUBLISH
Compose a JSON data type instance or instances from a variety of data sources, that is anything that can be referenced in an SQL statement. It can publish JSON data types of any storage format.
JSON_TABLE
Creates a temporary table based on all, or a subset, of the data in a JSON object.

SQL Stored Procedures

JSON_SHRED_BATCH and JSON_SHRED_BATCH_U
Allows you to extract values from JSON documents and use the extracted data to populate existing relational tables. This provides a flexible form of loading data from JSON format into the relational model.