JSON_VALUE

Purpose

This function extracts a single SQL value at a given path from a valid JSON string.

Usage Notes

• The parameter json_expr defines the input JSON string for the function. Exasol accepts an arbitrary expression of type (VAR)CHAR. To return an SQL value, json_expr has to follow the syntax rules on JSON.org.
• The parameter json_path_expr defines the JSON path for the function. Exasol accepts an arbitrary expression of type (VAR)CHAR. For the information on path syntax rules in Exasol, refer to the JSON Path Expressions section.
• The optional RETURNING data_type clause allows the specification of the SQL return type. The default type is VARCHAR(2000000).
• If JSON_VALUE contains ERROR ON EMPTY, it throws an error if the path does not identify an SQL value.
• If JSON_VALUE contains NULL ON EMPTY, it returns NULL if the path does not identify an SQL value. This is the default behavior.
• If JSON_VALUE contains DEFAULT expr ON EMPTY, it returns expr if the path does not identify an SQL value.
• If JSON_VALUE contains ERROR ON ERROR, it throws an error if an error occurs during the JSON processing.
• If JSON_VALUE contains NULL ON ERROR, it returns NULL if an error occurs during the JSON processing. This is the default behavior.
• If JSON_VALUE contains DEFAULT expr ON ERROR, it returns expr if an error occurs during the JSON processing.
• To learn more about the different possible errors, refer to the JSON Error Handling section.

Example

CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '{"name" : "Smith"}');
INSERT INTO json_input VALUES(2, '{"surname" : "Smith"}');
INSERT INTO json_input VALUES(3, '{"name" : 1');
INSERT INTO json_input VALUES(4, '{"name" : "Doe"}');

SELECT id,
       JSON_VALUE(json, '$.name' NULL ON EMPTY DEFAULT 'invalid name' ON ERROR) as "JSON value"
FROM json_input;

Result