Comparison of JSONExtract and JSONExtractValue

Teradata Vantageā„¢ JSON Data Type

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K
The JSONExtract, JSONExtractValue, and JSONExtractLargeValue methods extract values from a JSON instance.
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.
JSONExtractLargeValue
JSONExtractLargeValue is the same as JSONExtractValue, except for the return type and size.

The following table shows the main differences between the 3 methods.

Method Return Value Return Type Can Extract Multiple Values
JSONExtract An array of values or a JSON null JSON array Yes
JSONExtractValue A scalar value or a JSON null VARCHAR No
JSONExtractLargeValue A scalar value or a JSON null CLOB No
The following are some usage considerations:
  • By default, JSONExtractValue returns a VARCHAR(4096) of the desired character set; however, the size can be increased up to 32000 characters (not bytes) using the JSON_AttributeSize field in DBS Control. If the result cannot be contained in the return length of this method (even after increasing the return length to the maximum of 32000 characters), you should use the JSONExtractLargeValue method instead.
  • JSONExtractLargeValue functions identically to JSONExtractValue, but it returns a CLOB of 16776192 characters for CHARACTER SET LATIN or 8388096 characters for CHARACTER SET UNICODE, depending on the character set of the JSON type that invoked it.
  • The JSONExtractValue and JSONExtractLargeValue methods only extract a single scalar value or a JSON null. If more than one value matches the JSONPath query expression, a warning and an error message string indicating that multiple results were found are returned. Use JSONExtract if you want to extract multiple values from the JSON instance.

The reason to choose one method over the other depends on how you want to use the result. For example, if you want to extract a single scalar value from a JSON instance and treat the value as some predefined type which is castable from VARCHAR, use JSONExtractValue. If you want to extract a nested JSON object/array and do further processing on the data, treating it as a JSON object, use JSONExtract.