Example: JSONExtractValue with Multiple Results Error - 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ā„¢

This example shows the warning and error message string returned when JSONExtractValue finds multiple results to extract. The query attempts to get the name of every school for each person.

JSONExtractLargeValue can be substituted for JSONExtractValue.
SELECT eno, edata.JSONExtractValue('$..schools..name')
FROM my_table 
ORDER BY 1;
Result:
*** Query completed. 4 rows found. 2 columns returned.  
*** Warning: 7548 More than one result per JSON instance found. 
*** Total elapsed time was 1 second. 

eno edata.JSONEXTRACTVALUE('$..schools..name')
--------------------------------------------------
1   *** ERROR MULTI RESULT ***
2   *** ERROR MULTI RESULT ***
3   *** ERROR MULTI RESULT ***
4   *** ERROR MULTI RESULT ***