Comparison of JSONExtract and JSONExtractValue - Advanced SQL Engine - Teradata Database

JSON Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
uwa1591040057999.ditamap
dita:ditavalPath
uwa1591040057999.ditaval
dita:id
B035-1150
lifecycle
previous
Product Category
Teradata Vantageā„¢
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.