Extracting JSON Data Examples | Teradata Vantage - Extracting JSON Data Examples - 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ā„¢

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

The following query returns a warning and error message string because it uses JSON dot notation to extract more than one object member. If you want a list of values 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 dot notation returns multiple results:
  • Recursive descent operator
  • Wildcard
  • Name or index list
  • Slice
For more information, see JSON Dot Notation (Entity Reference).
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 dot notation 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"]