Example: Error: JSON Entity Reference With Multiple Results

Teradata Vantageā„¢ JSON Data Type

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

This example shows a JSON entity reference where multiple array element results are found and the warning and error message string that are returned. 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.

Example

SELECT id, jsonCol.numbers
FROM test.jsonTable
WHERE id < 3
ORDER BY id;
Result:
*** Query completed. 2 rows found. 2 columns returned.  
*** Warning: 7548 More than one result per JSON instance found. 
*** Total elapsed time was 1 second.

id     jsonCol.numbers
------------------------
1      *** ERROR MULTI RESULT ***
2      ?                /* There are no numbers in this JSON */