Extracting JSON Data Examples | Teradata Vantage - Examples: Extracting JSON Data - 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 uses JSON dot notation to extract more than one object member, and therefore returns a warning and error message string. If you want a list of values returned instead, you must specify this behavior using the SET SESSION DOT NOTATION ON ERROR statement.

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"]