JSON Error Handling

This article describes error handling in the JSON_VALUE and JSON_EXTRACT functions.

Errors can occur when json_expr and json_path_expr input is processed by the JSON_VALUE and JSON_EXTRACT functions. The following table is an overview of the different error types and shows how the JSON_VALUE and JSON_EXTRACT functions in Exasol react to the 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