15.10 - JSONExtractValue and JSONExtractLargeValue - Teradata Database

Purpose

The JSONExtractValue and JSONExtractLargeValue methods retrieve the text representation of the value of an entity in a JSON instance. JSONExtractLargeValue takes the same input parameters and operates the same as JSONExtractValue; however, the return type and size are different.

Syntax

Syntax Elements

JSON_expr

An expression that evaluates to a JSON data type.

JSONPath_expr

An expression to extract information about a particular portion of a JSON instance.

The retrieved data can be any portion of a JSON instance; for example, a name/value pair, object, array, array element, or a value, as long as the returnable part is a scalar value such as a string, number, boolean, or null.

The JSONPath expression must be in JSONPath syntax.

JSONPath_expr cannot be NULL. If the expression is NULL, an error is reported.

Functional Description

These methods search the JSON object specified by JSON_expr and get the value of the entity name specified by JSONPath_expr. The entity name is represented by a JSONPath formatted string.

JSONPath_expr should select zero JSON items or a single JSON item, which must be either a scalar value or a JSON null. If more than one value matches JSONPath_expr, a warning and an error message string indicating that multiple results were found are returned.

Return Value

• A string that is the value of the entity, if the entity was found in the JSON instance.
• A warning and an error message string "*** ERROR MULTI RESULT ***" if more than one value matches the query expression specified by JSONPath_expr.
• An empty string if the result is an empty string.
• A Teradata NULL, if the result is a JSON null.
• A Teradata NULL, if the entity was not found in the JSON object.
• A Teradata NULL, if the JSON_expr argument is null.

JSONExtractValue returns a VARCHAR of the desired attribute. The returned length defaults to 4K, but can be increased to 32000 characters (not bytes) using the DBS Control field JSON_AttributeSize. If the result of the method is too large for the buffer, an error is reported.

JSONExtractLargeValue extracts a scalar value up to the maximum size of the type. It returns a CLOB of 16776192 characters for CHARACTER SET LATIN or 8388096 characters for CHARACTER SET UNICODE.

The return value of both methods can be UNICODE or LATIN, depending on the character set of the JSON type that invoked it. If the parameter character set does not match the character set of the JSON type, Teradata attempts to translate the parameter character set to the correct character set.

You can cast the return string to any data type that supports casting.

Usage

JSONExtractLargeValue should only be used when the results of the extraction are large (greater than 32000 characters); otherwise use JSONExtractValue.