JSON Error Handling
Different errors can occur during the processing of the json_expr and json_path_expr input of the JSON_VALUE and JSON_EXTRACT functions. This page gives an overview of the different error types and shows how the JSON_VALUE and JSON_EXTRACT functions in Exasol react to the occurring errors.
Most examples use JSON_VALUE for simplicity. However, all examples are also applicable to JSON_EXTRACT.
Error Description | Examples | Result |
---|---|---|
json_expr is not of type (VAR)CHAR |
SELECT JSON_VALUE(1, '$'); |
Exception: JSON_VALUE expects string for JSON parameter |
json_path_expr is not of type (VAR)CHAR |
SELECT JSON_VALUE('1', 1); |
Exception: JSON_VALUE expects string for path parameter |
json_path_expr is NULL |
SELECT JSON_VALUE('1', NULL); |
Exception: data exception - invalid JSON path: path is empty or NULL |
json_path_expr is invalid | SELECT JSON_VALUE('1', '$['); |
Exception: data exception - invalid JSON path: no ']' found at position 2: '$[' |
json_expr is NULL |
SELECT JSON_VALUE(NULL, '$.name'); |
NULL |
json_expr is invalid and NULL ON ERROR is set |
SELECT JSON_VALUE('{"name" : "John"', '$.name' NULL ON ERROR); |
NULL |
json_expr is invalid and ERROR ON ERROR is set |
SELECT JSON_VALUE('{"name" : "John"', '$.name' ERROR ON ERROR); |
Exception: data exception - invalid JSON text: , or } expected at position 17 |
json_path_expr points to a non-existing value and NULL ON EMPTY is set |
SELECT JSON_VALUE('[1, 2, 3]', '$[4]' NULL ON EMPTY); |
NULL |
SELECT JSON_VALUE('{"name" : "John"}', '$.surname' NULL ON EMPTY); |
||
json_path_expr points to a non-existing value and ERROR ON EMPTY is set |
SELECT JSON_VALUE('[1, 2, 3]', '$[4]' ERROR ON EMPTY); |
Exception: data exception - no SQL/JSON item |
SELECT JSON_VALUE('{"name" : "John"}', '$.surname' ERROR ON EMPTY); |
||
json_path_expr points to multiple values and NULL ON ERROR is set |
SELECT JSON_VALUE('[1, 2, 3]', '$[*]' NULL ON ERROR); |
NULL |
SELECT JSON_VALUE('{"a" : 1, "b" : 2}', '$.*' NULL ON ERROR); |
||
json_path_expr points to multiple values and ERROR ON ERROR is set |
SELECT JSON_VALUE('[1, 2, 3]', '$[*]' ERROR ON ERROR); |
Exception: data exception - more than one SQL/JSON item |
SELECT JSON_VALUE('{"a" : 1, "b" : 2}', '$.*' ERROR ON ERROR); |
||
json_path_expr does not point to a JSON scalar value and NULL ON ERROR is set |
SELECT JSON_VALUE('[1, 2, 3]', '$' NULL ON ERROR); |
NULL |
SELECT JSON_VALUE('{"name" : "John"}', '$' NULL ON ERROR); |
||
json_path_expr does not point to a JSON scalar value and ERROR ON ERROR is set |
SELECT JSON_VALUE('[1, 2, 3]', '$' ERROR ON ERROR); |
Exception: data exception - SQL/JSON scalar required |
SELECT JSON_VALUE('{"name" : "John"}', '$' ERROR ON ERROR); |
||
json_path_expr returns the wrong data type and NULL ON ERROR is set |
SELECT JSON_VALUE('"a"', '$' RETURNING INT NULL ON ERROR); |
NULL |
SELECT JSON_EXTRACT('"a"', '$ null on error') EMITS (a INT); |
||
json_path_expr returns the wrong data type and ERROR ON ERROR is set |
SELECT JSON_VALUE('"a"', '$' RETURNING INT ERROR ON ERROR); |
Exception: data exception - invalid character value for cast; Value: 'a' |
SELECT JSON_EXTRACT('"a"', '$ error on error') EMITS (col1 INT); |
Exception: data exception - cast error in column: COL1 |