Extracting JSON Data Using SELECT and JSON Methods

Teradata Vantageā„¢ JSON Data Type

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

JSON data can be extracted from JSON documents stored in the Teradata database using the SELECT statement or by using JSON methods: JSONExtract, JSONExtractValue, JSONExtractLargeValue, JSON_TABLE, JSON_SHRED_BATCH, and JSON_SHRED_BATCH_U.

To adhere to the proposed ANSI SQL/JSON standard, the extraction methods return one value that matches the desired path. The first result found is returned, except if one of the following elements is present in the JSONPath query string.

  • Recursive descent operation
  • Wildcard
  • Name or index list
  • Index slice
  • Filter
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.

The search is optimized in that it does not always need to search the entire document. However, this means that the following scenario is possible.

  1. Disable JSON validation
  2. Insert malformed JSON data. For example, something similar to this: {"name":"Cameron" 123456}
  3. Enable JSON validation
  4. Attempt to extract the value of the key "name"

In this case, the correct result of "Cameron" is returned, because the result was found before passing through the entire document and reaching the malformed data. If any malformed data is encountered during the search, before the result is reached, a syntax error is reported.

Example: JSON Entity Reference, JSONExtractValue, and JSONExtract

You can use the SELECT statement with the JSON entity reference syntax or the JSONExtractValue (or JSONExtractLargeValue) method to extract a single scalar value from a JSON instance. However, if you try to use JSON entity reference or the JSONExtractValue (or JSONExtractLargeValue) method to extract more than one value, you will get a warning and an error message string by default.

The following query returns a warning and error message string because JSON entity reference is used to extract more than one object member. If you want a list of values to be returned instead, you must specify this behavior using the SET SESSION DOT NOTATION...ON ERROR statement or the DotNotationOnErrorCondition DBS Control field. Note that using the following syntax elements in JSON entity reference will likely return multiple results:
  • Recursive descent operator
  • Wildcard
  • Name or index list
  • Slice
For more information, see JSON Entity Reference (Dot Notation Syntax).
SELECT NEW JSON('{"name":"Al","name":"Betty"}').name;

Result:

*** Query completed. One row found. One column returned.
*** Warning: 7548 More than one result per JSON instance found.
*** Total elapsed time was 1 second.

NEW JSON('{"name":"Al","name":"Betty"}', LATIN).name
-----------------------------------------------------------------
*** ERROR MULTI RESULT ***

The same query using JSONExtractValue instead of JSON entity reference also returns a warning and an error message string.

SELECT NEW JSON('{"name":"Al","name":"Betty"}').JSONExtractValue('$.name');

Result:

*** Query completed. One row found. One column returned.
*** Warning: 7548 More than one result per JSON instance found.
*** Total elapsed time was 1 second.

NEW JSON('{"name":"Al","name":"Betty"}', LATIN).JSONEXTRACTVALUE('$.name')
---------------------------------------------------------------------------
*** ERROR MULTI RESULT ***

You can use the JSONExtract method to extract multiple values. The same query using the JSONExtract method succeeds and returns the multiple values in a JSON array.

SELECT NEW JSON('{"name":"Al","name":"Betty"}').JSONExtract('$.name');

Result:

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

NEW JSON('{"name":"Al","name":"Betty"}', LATIN).JSONEXTRACT('$.name')
---------------------------------------------------------------------------
["Al","Betty"]