15.10 - JSONExtract - Teradata Database

Purpose

The JSONExtract method operates on a JSON instance, to extract data identified by the JSONPath formatted string. If one or more entities are found, the result of this method is a JSON array composed of the values found; otherwise, NULL is returned.

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. For example, $.employees.info[*] provides all the information about each employee.

The desired information can be any portion of a JSON instance; for example, a name/value pair, object, array, array element, or a value.

The JSONPath expression must be in JSONPath syntax.

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

Functional Description

JSONExtract searches the JSON object specified by JSON_expr and retrieves the data that matches the entity name specified by JSONPath_expr.

Return Value

• A JSON array whose elements are all the matches for the JSONPath_expr in the JSON instance.
• NULL, if the entity was not found in the JSON object.
• NULL, if the JSON_expr argument is null.
• If the JSONPath_expr is null, an error is returned.

The return value can be either UNICODE or LATIN, depending on the character set of the JSON type that invoked this method. 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.