15.10 - Example: Difference Between JSONExtract and JSONExtractValue - Teradata Database

Teradata Database Teradata JSON

Product
Teradata Database
Release Number
15.10
Published
December 2015
Language
English (United States)
Last Update
2018-06-05
dita:mapPath
TeradataJSON_15_10.ditamap
dita:ditavalPath
ft:empty

Error: Using JSONExtractValue to Extract More Than One Value

In this example, a warning and error message string are returned when the JSONExtractValue method is used to extract more than a single scalar value.

SELECT NEW JSON('{"numbers":[1,2,3]}').JSONExtractValue('$.numbers.*');

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('{"numbers":[1,2,3]}', LATIN).JSONEXTRACTVALUE('$.numbers.*')
---------------------------------------------------------------------------
*** ERROR MULTI RESULT ***

Using JSONExtract to Retrieve Multiple Values

The following is the same as the previous example except the JSONExtract method is used instead of the JSONExtractValue method. In this case, the multiple values are returned in a JSON array.

SELECT NEW JSON('{"numbers":[1,2,3]}').JSONExtract('$.numbers.*');

Result:

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

NEW JSON('{"numbers":[1,2,3]}', LATIN).JSONEXTRACT('$.numbers.*')
---------------------------------------------------------------------------
[1,2,3]