JSONExtract

Teradata Vantageā„¢ JSON Data Type

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

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



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.