This section describes the different elements that Exasol supports from the SQL/JSON path language, along with a short description and an example.
All parts of the SQL/JSON path language that are not mentioned in this section are not supported by Exasol.
Category: Prefix
The SQL/JSON path language supports the two prefixes 'strict' and 'lax' (also called JSON path modes). Exasol only supports the prefix 'lax'.
Lax
It is possible to add 'lax' as a prefix to any path. As Exasol only supports 'lax' path expressions, the prefix has no effect on the query result.
Examples
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '{"first name" : "Claire", "surname" : "Doe"}');
INSERT INTO json_input VALUES(2, '{"first name" : "John", "age" : 20}');
SELECT id, JSON_VALUE(json, '$.surname') AS RESULT_VALUE FROM json_input;Result
| ID | RESULT_VALUE |
| 1 | Doe |
| 2 | NULL |
Result
| ID | RESULT_VALUE |
| 1 | Doe |
| 2 | NULL |
Category: Variables
The SQL/JSON path language supports several variables. As Exasol supports arbitrary expressions as json_path_expr, it does not need to support the PASSING clause and 'JSON path named variables'. As Exasol does not support filters, it does not support the '@' variable.
JSON Path Context Variable
The context variable '$' is the entry point for any JSON path.
Examples
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, 'true');
INSERT INTO json_input VALUES(2, 'false');
INSERT INTO json_input VALUES(3, 'null');
INSERT INTO json_input VALUES(4, '42');
INSERT INTO json_input VALUES(5, '1.0');
INSERT INTO json_input VALUES(6, '"Exasol"');
SELECT id, JSON_VALUE(json, '$') AS RESULT_VALUE FROM json_input;Result
| ID | RESULT_VALUE |
| 1 | true |
| 2 | false |
| 3 | NULL |
| 4 | 42 |
| 5 | 1.0 |
| 6 | Exasol |
Category: Accessors
With accessors it is possible to navigate through a JSON structure.
Member Accessor
A member accessor gives access to a JSON object member by its name. It is possible to concatenate an arbitrary number of names to access nested objects.
The member accessor is case-sensitive. If a name starts with the character '$' or the name contains special characters like white space, it is necessary to enclose the name with double-quotes. The member accessor adheres to the lexical rules of ECMAScript identifiers.
Examples
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '{"customer" : { "first name" : "Ann" } }');
INSERT INTO json_input VALUES(2, '{"first name" : "Adam" }');
INSERT INTO json_input VALUES(4, '{"Forename" : "Luke" }');
INSERT INTO json_input VALUES(5, '{"$Forename" : "Sue" }');
INSERT INTO json_input VALUES(6, '{"Forename and Surname" : "John Doe" }');
SELECT id,
JSON_VALUE(json, '$.customer."first name"') AS CUSTOMER_FORENAME,
JSON_VALUE(json, '$."first name"') AS "first name",
JSON_VALUE(json, '$.Forename') AS "Forename",
JSON_VALUE(json, '$."$Forename"') AS "$Forename",
JSON_VALUE(json, '$."Forename and Surname"') AS "Forename and Surname"
FROM json_input;Result
| ID | CUSTOMER_FORENAME | first name | Forename | $Forename | Forename and Surname |
| 1 | Ann | NULL | NULL | NULL | NULL |
| 2 | NULL | Adam | NULL | NULL | NULL |
| 3 | NULL | NULL | Luke | NULL | NULL |
| 4 | NULL | NULL | NULL | Sue | NULL |
| 5 | NULL | NULL | NULL | NULL | John Doe |
Wildcard Member Accessor
The wildcard member accessor '.*' gives access to all JSON object members independent from their name.
Examples
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '{"customer" : { "first name" : "Ann" } }');
INSERT INTO json_input VALUES(2, '{"customer" : { "first name" : "John" } }');
INSERT INTO json_input VALUES(3, '{"customer" : { "first name" : "Ann", "surname" : "Doe" } }');
SELECT id,
JSON_VALUE(json, '$.*.first name') AS FORENAME,
JSON_VALUE(json, '$.*.*') AS DOUBLE_ASTERISK,
JSON_VALUE(json, '$.*.surname') AS SURNAME
FROM json_input;Result
| ID | FIRTS NAME | DOUBLE_ASTERISK | SURNAME |
| 1 | Ann | Ann | NULL |
| 2 | John | John | NULL |
| 3 | Ann | NULL | Doe |
Element Accessor
With an element accessor, it is possible to select one or more indexes from a JSON array. Exasol supports access to single elements as well as ranges. It is also possible to combine single element accesses with range accesses. However, Exasol does not support overlapping or unordered indexes.
Examples
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '["First", "Second", "Third"]');
INSERT INTO json_input VALUES(2, '[["First", "Second"], ["Third"]]');
INSERT INTO json_input VALUES(3, '[1, 2, 3, 4, {"name" : "Ann"}, 6]');
SELECT id,
JSON_VALUE(json, '$[0]') AS FIRST_ELEMENT,
JSON_VALUE(json, '$[2]') AS THIRD_ELEMENT,
JSON_VALUE(json, '$[0][1]') AS NESTED_ELEMENT,
JSON_VALUE(json, '$[4].name') AS NAME,
JSON_VALUE(json, '$[1, 2 to 3, 4 to 5, 6].name') AS NAME_WITH_RANGE
FROM json_input;Result
| ID | FIRST_ELEMENT | THIRD_ELEMENT | NESTED_ELEMENT | NAME | NAME_WITH_RANGE |
| 1 | First | Third | NULL | NULL | NULL |
| 2 | NULL | NULL | Second | NULL | NULL |
| 3 | 1 | 3 | NULL | Ann | Ann |
SELECT id, JSON_VALUE(json, '$[0,0]') FROM json_input;
-- Returns error: data exception - invalid JSON path: array indices overlap
SELECT id, JSON_VALUE(json, '$[0 to 5, 1 to 7]') FROM json_input;
--returns error: data exception - invalid JSON path: array indices overlap
SELECT id, JSON_VALUE(json, '$[3, 1]') FROM json_input;
--returns error: data exception - invalid JSON path: array indices not in ascending orderWildcard Element Accessor
The wildcard element accessor '[*]' gives access to all elements in an array. As Exasol always uses lax mode, it is not necessary to specify the wildcard element accessor for a single array.
It is only necessary to use the wildcard element accessor for nested arrays. If the wildcard element accessor is applied to a JSON value that is not an array, the lax mode wraps the value automatically into an array.
Examples
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '{ "customers" : [ { "name" : "John" }, { "first name" : "Eve" } ] }');
INSERT INTO json_input VALUES(2, '{ "customers" : [ null, [ { "name" : "John" }, { "first name" : "Eve" } ] ] }');
SELECT id,
JSON_VALUE(json, '$.customers[*].name') AS WILDCARD,
JSON_VALUE(json, '$.customers.name') AS WITHOUT_WILDCARD,
JSON_VALUE(json, '$.customers.name[*]') AS NON_ARRAY_WILDCARD,
JSON_VALUE(json, '$.customers[*][*].name[*]') AS DOUBLE_WILDCARD
FROM json_input;Result
| ID | WILDCARD | WITHOUT_WILDCARD | NON_ARRAY_WILDCARD | DOUBLE_WILDCARD |
| 1 | John | John | John | John |
| 2 | John | NULL | NULL | John |
Category: Item Functions
It is possible to call functions on an SQL/JSON value defined by a path. This section lists the predefined functions that Exasol supports.
Function type()
The function type() returns the type of the SQL/JSON value defined by a path. The return type is (VAR)CHAR.
Examples
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, NULL);
INSERT INTO json_input VALUES(2, '{"name" : "John"}');
INSERT INTO json_input VALUES(3, '[{"name" : "John"}]');
INSERT INTO json_input VALUES(4, 'true');
INSERT INTO json_input VALUES(5, 'false');
INSERT INTO json_input VALUES(6, 'null');
INSERT INTO json_input VALUES(7, '42');
INSERT INTO json_input VALUES(8, '1.0');
INSERT INTO json_input VALUES(9, '"Exasol"');
SELECT id, JSON_VALUE(json, '$.type()') AS JSON_RESULT
FROM json_input;Result
| ID | JSON_RESULT |
| 1 | NULL |
| 2 | object |
| 3 | array |
| 4 | boolean |
| 5 | boolean |
| 6 | null |
| 7 | number |
| 8 | number |
| 9 | string |
Function size()
The function size() returns the size of a JSON array. For all other JSON values it returns 1.
Examples
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '{"first name" : "Ann", "surename" : "Smith"}');
INSERT INTO json_input VALUES(2, '["First", "Second", "Third"]');
INSERT INTO json_input VALUES(3, '[["First", "Second"], ["Third"]]');
INSERT INTO json_input VALUES(4, 'true');
INSERT INTO json_input VALUES(5, 'false');
INSERT INTO json_input VALUES(6, 'null');
INSERT INTO json_input VALUES(7, '42');
INSERT INTO json_input VALUES(8, '1.0');
INSERT INTO json_input VALUES(9, '"Exasol"');
SELECT id, json, JSON_VALUE(json, '$.size()') AS FUNCTION_SIZE
FROM json_input;Result
| ID | JSON | FUNCTION_SIZE |
| 1 | {"first name" : "Ann", "surename" : "Smith"} | 1 |
| 2 | ["First", "Second", "Third"] | 3 |
| 3 | ["First", "Second", "Third"] | 2 |
| 4 | true | 1 |
| 5 | false | 1 |
| 6 | null | 1 |
| 7 | 42 | 1 |
| 8 | 1.0 | 1 |
| 9 | "Exasol" | 1 |
Category: Exasol Extensions
For manual exploration of JSON data and for debugging purposes, Exasol provides several extensions to the SQL/JSON path language. All of the extensions in this category are Exasol-specific and not part of the SQL standard.
Function json()
The function json() converts the JSON value (or values) specified by a path prefix into a JSON string. A possible application of json() is the extraction of complete JSON objects or JSON arrays as (VAR)CHAR. Note that json() removes insignificant white space between JSON elements from the original JSON input.
Examples
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '{ "customers" : [ { "name" : "John Doe" }, { "first name" : "Eve" } ] }');
INSERT INTO json_input VALUES(2, '["First", "Second", "Third"]');
INSERT INTO json_input VALUES(3, '"Exasol"');
SELECT id,
JSON_VALUE(json, '$.json()') AS COMPLETE_JSON,
JSON_VALUE(json, '$.customers.json()') AS JSON_ARRAY,
JSON_VALUE(json, '$.customers[0].json()') AS JSON_OBJECT
FROM json_input;Result
| ID | COMPLETE_JSON | JSON_ARRAY | JSON_OBJECT |
| 1 | {"customers":[{"name":"John Doe"},{"first name":"Eve"}]} | [{"name":"John Doe"},{"first name":"Eve"}] | {"name":"John Doe"} |
| 2 | ["First","Second","Third"] | NULL | NULL |
| 3 | "Exasol" | NULL | NULL |
Function path()
The function path() returns the actual path that Exasol uses as (VAR)CHAR. This can help to debug paths with wildcards.
Examples
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '{ "customers" : [ { "name" : "John" } ] }');
INSERT INTO json_input VALUES(2, '{ "name list" : [ { "name" : "Ann" } ] }');
INSERT INTO json_input VALUES(3, '{ "customers" : [ null, null, { "name" : "Smith" } ] }');
INSERT INTO json_input VALUES(4, '{ "telephone list" : [ { "number" : "+49123456789" } ] }');
SELECT id,
JSON_VALUE(json, '$.*.*') AS JSON_RESULT,
JSON_VALUE(json, '$.*.*.path()') AS PATH_FUNCTION
FROM json_input;
Results
| ID | JSON_RESULT | PATH_FUNCTION |
| 1 | John | $.customers[0].name |
| 2 | Ann | $."name list"[0].name |
| 3 | Smith | $.customers[2].name |
| 4 | +49123456789 | $."telephone list"[0].number |
Function jsonpath()
The function jsonpath() determines the actual path that Exasol uses and splits it into an array of accessors. It returns a (VAR)CHAR result that contains a JSON array with the identified accessors of the path.
Examples
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '{ "customers" : [ { "name" : "John" } ] }');
INSERT INTO json_input VALUES(2, '{ "name list" : [ { "name" : "Ann" } ] }');
INSERT INTO json_input VALUES(3, '{ "customers" : [ null, null, { "name" : "Smith" } ] }');
INSERT INTO json_input VALUES(4, '{ "telephone list" : [ { "number" : "+49123456789" } ] }');
SELECT id,
JSON_VALUE(json, '$.*.*') AS JSON_RESULT,
JSON_VALUE(json, '$.*.*.path()') AS PATH_FUNCTION,
JSON_VALUE(json, '$.*.*.jsonpath()') AS JSONPATH_FUNCTION
FROM json_input;Results
| ID | JSON_RESULT | PATH_FUNCTION | JSONPATH_FUNCTION |
| 1 | John | $.customers[0].name | ["customers",0,"name"] |
| 2 | Ann | $."name list"[0].name | ["name list",0,"name"] |
| 3 | Smith | $.customers[2].name | ["customers",2,"name"] |
| 4 | +49123456789 | $."telephone list"[0].number | ["telephone list",0,"number"] |
Function name()
The function name() returns the name of a name/value pair defined in a JSON object.
Examples
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '{ "customers" : [ { "first name" : "John" } ] }');
INSERT INTO json_input VALUES(2, '{ "telephone list" : [ { "number" : "+49123456789" } ] }');
SELECT id,
JSON_VALUE(json, '$.*.name()') AS JSON_NAME,
JSON_VALUE(json, '$.*.*.name()') AS NESTED_JSON_NAME,
JSON_VALUE(json, '$.*.*') AS NESTED_JSON_VALUE
FROM json_input;Results
| ID | JSON_NAME | NESTED_JSON_NAME | NESTED_JSON_VALUE |
| 1 | customers | first name | John |
| 2 | telephone list | number | +49123456789 |
Function id()
The function id() prints a unique identifier for each JSON element. The identifier is only unique within one JSON input.
Examples
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '[{"first name" : "Nick"}, {"first name" : "Eve"}]');
INSERT INTO json_input VALUES(2, '[{"first name" : "Ann"}, {"surename" : "Smith", "first name" : "John"}]');
SELECT id,
JSON_VALUE(json, '$[0].id()') AS ELEMENT_ID_0,
JSON_VALUE(json, '$[0]."first name".id()') AS NAME_ID_0,
JSON_VALUE(json, '$[0]."first name"') AS VALUE_ID_0,
JSON_VALUE(json, '$[1].id()') AS ELEMENT_ID_1,
JSON_VALUE(json, '$[1]."first name".id()') AS NAME_ID_1,
JSON_VALUE(json, '$[1]."first name"') AS VALUE_ID_1
FROM json_input;Results
| ID | ELEMENT_ID_0 | NAME_ID_0 | VALUE_ID_0 | ELEMENT_ID_1 | NAME_ID_1 | VALUE_ID_1 |
| 1 | 1 | 3 | Nick | 4 | 6 | Eve |
| 2 | 1 | 3 | Ann | 4 | 8 | John |
Function position()
The function position() returns the position of an element within an array. The index of the first element in an array is zero. Similarly, for elements that are not part of an array, position() returns 0. This can help in identifying specific data elements.
Examples
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '[{"first name" : "Nick", "name" : "foo"}, {"first name" : "Eve"}]');
INSERT INTO json_input VALUES(2, '{"company" : "Exasol"}');
INSERT INTO json_input VALUES(3, '"Exasol"');
SELECT id,
JSON_VALUE(json, '$[1].position()') AS POSITION_1,
JSON_VALUE(json, '$[1]."first name".position()') AS FORENAME_POSITION,
JSON_VALUE(json, '$.position()') AS CONTEXT_POSITION
FROM json_input;Results
| ID | POSITION_1 | FORENAME_POSITION | CONTEXT_POSITION |
| 1 | 1 | 0 | 0 |
| 2 | NULL | NULL | 0 |
| 3 | NULL | NULL | 0 |
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '{"customers" : [{"name" : "Nick"}, {"name" : "Eve"}]}');
INSERT INTO json_input VALUES(2, '{"customers" : [{"name" : "Ann"}, {"name" : "John"}]}');
SELECT id,
JSON_EXTRACT(json, '$.customers#.position()', '$.customers#.name')
EMITS (JSON_POSITION INT, NAME VARCHAR(100))
FROM json_input;Results
| ID | JSON_POSITION | NAME |
| 1 | 0 | Nick |
| 1 | 1 | Eve |
| 2 | 0 | Ann |
| 2 | 1 | John |
Function toarray()
The function toarray() returns all SQL/JSON values identified by the path as an array. This is useful to identify paths that return more than one value.
Examples
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '{"customers" : [{"name" : "Nick"}, {"name" : "Eve"}]}');
INSERT INTO json_input VALUES(2, '{"customers" : [{"name" : "Ann"}], "family" : [{"name" : "John"}]}');
INSERT INTO json_input VALUES(3, '{"contacts" : {"name" : "Chris", "name" : "Kate"}}');
INSERT INTO json_input VALUES(4, '"Exasol"');
SELECT id,
JSON_VALUE(json, '$.customers.name.json().toarray()') as CUSTOMERS_AS_JSON,
JSON_VALUE(json, '$.customers[*].json().toarray()') as CUSTOMER_ENTRIES,
JSON_VALUE(json, '$.*.name.toarray()') as ALL_NAMES,
JSON_VALUE(json, '$.*.name.json().toarray()') as ALL_NAMES_AS_JSON,
JSON_VALUE(json, '$.toarray()') as CONTEXT_AS_ARRAY
FROM json_input;Results
| ID | CUSTOMERS_AS_JSON | CUSTOMER_ENTRIES | ALL_NAMES | ALL_NAMES_AS_JSON | CONTEXT_AS_ARRAY |
| 1 | ["Nick","Eve"] | [{"name":"Nick"},{"name":"Eve"}] | [Nick,Eve] | ["Nick","Eve"] | NULL |
| 2 | ["Ann"] | [{"name":"Ann"}] | [Ann,John] | ["Ann","John"] | NULL |
| 3 | [] | [] | [Chris,Kate] | ["Chris","Kate"] | NULL |
| 4 | [] | [] | [] | [] | [Exasol] |
Multi-Level Wildcard Accessor
With this accessor it is possible to select all leaf values of a JSON input at once.
Examples
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '{"customers" : [{"name" : "Nick"}, {"name" : "Eve"}]}');
INSERT INTO json_input VALUES(2, '{"customers" : [{"name" : "Ann"}], "family" : [{"name" : "John"}]}');
INSERT INTO json_input VALUES(3, '{"contacts" : {"name" : "Chris", "name" : "Kate"}, "telephone list" : [ { "number" : "+49123456789" } ] } ');
INSERT INTO json_input VALUES(4, '"Exasol"');
INSERT INTO json_input VALUES(5, '[1, 2, 3, 4, 5, {"measurement" : {"run" : "first run", "value" : 42.0}}]');
SELECT id, JSON_VALUE(json, '$.**.json().toarray()') as CUSTOMERS_AS_JSON
FROM json_input;Results
| ID | CUSTOMERS_AS_JSON |
| 1 | ["Nick","Eve"] |
| 2 | ["Ann","John"] |
| 3 | ["Chris","Kate","+49123456789"] |
| 4 | ["Exasol |
| 5 | [1,2,3,4,5,"first run",42.0] |
Category: JSON_EXTRACT Extensions
JSON_EXTRACT is an Exasol-specific function to convert JSON into a relational format during ETL jobs. For this purpose, there are several path extensions that are only available in JSON_EXTRACT.
Extract Multiple Rows From Nested Data
With JSON_EXTRACT it is possible to extract several rows from a single JSON input. If a path specifies more than one JSON value, it is possible to split the values into several rows with the '#' path extension. However, a single JSON_EXTRACT call can only split up paths inside the same hierarchy chain. It is not possible to split up two independent paths within the same JSON_EXTRACT call.
Examples
CREATE OR REPLACE TABLE json_input2(id INT, json VARCHAR(2000000));
INSERT INTO json_input2 VALUES(1, '{"data" : [{"country" : "United Kingdom", "customers" : [{"name" : "John Doe", "phone numbers" : [{"type" : "private", "number" : "+4412345678"}]}]}]}');
INSERT INTO json_input2 VALUES(2, '{"data" : [{"country" : "Germany", "customers" : [{"name" : "Hans Meier", "phone numbers" : [{"type" : "private", "number" : "+4912345678"}, {"type" : "mobile", "number" : "+49987654321"}]} ,{"name" : "Achim Schmidt", "phone numbers" : [{"type" : "private", "number" : "+49214365879"}, {"type" : "mobile", "number" : "+4989674523"}]}]}]}');
SELECT id,
JSON_EXTRACT(json,
'$.data.country',
'$.data.customers#.name',
'$.data.customers#."phone numbers"#.type',
'$.data.customers#."phone numbers"#.number'
)
EMITS(
country VARCHAR(100),
name VARCHAR(100),
phone_number_type VARCHAR(100),
phone_number VARCHAR(100)
)
FROM json_input2
ORDER BY id, name, phone_number;Results
| ID | COUNTRY | NAME | PHONE_NUMBER_TYPE | PHONE_NUMBER |
| 1 | United Kingdom | John Doe | private | +4412345678 |
| 2 | Germany | Achim Schmidt | private | +49214365879 |
| 2 | Germany | Achim Schmidt | mobile | +4989674523 |
| 2 | Germany | Hans Meier | private | +4912345678 |
| 2 | Germany | Hans Meier | mobile | +49987654321 |
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '{"customers" : {"name" : "Ann"}, "family" : {"name" : "Michael"}}');
INSERT INTO json_input VALUES(2, '{"customers" : [{"name" : "Eve"}, {"name" : "Joe"}], "family": [{"name" : "Sven"}]}');
SELECT id,
JSON_EXTRACT(json, '$.*.name#.id()',
'$.*.name#'
)
EMITS (
GENERATED_ID INT,
NAME VARCHAR(1000)
)
FROM json_input;Results
| ID | GENERATED_ID | NAME |
| 1 | 4 | Ann |
| 1 | 8 | Micheal |
| 2 | 5 | Eve |
| 2 | 8 | Joe |
| 2 | 13 | Sven |
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '{"customers" : [{"name" : "Nick"}, {"name" : "Eve"}], "articles" : [{"item number" : 42}, {"item number" : 234}}]');
SELECT id,
JSON_EXTRACT(json,
'$.customers#.name',
'$.articles#."item number"'
)
EMITS (
CUSTOMERS VARCHAR(1000),
ITEM_NUMBERS VARCHAR(1000)
)
FROM json_input;
--ERROR: data exception - invalid JSON path: All nested path prefixes ending with a '#' character must be a prefix of the longest nested path.Function error()
The default behavior of JSON_EXTRACT is ERROR ON EMPTY and ERROR ON ERROR for all paths. It is possible to switch that behavior to LOG ON EMPTY and LOG ON ERROR by adding the special path '$.error()' as last element to the JSON_EXTRACT arguments. If LOG ON EMPTY or LOG ON ERROR is specified, JSON_EXTRACT writes all errors into an error column.
Examples
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '{"customers" : [{"name" : "Nick"}, {"name" : "Eve"}]}');
SELECT JSON_EXTRACT(json,
'$.articles."item number"'
)
EMITS (
ITEM_NUMBERS VARCHAR(1000)
)
FROM json_input;
--ERROR: data exception - no SQL/JSON item in column: ITEM_NUMBERSSELECT ID,
JSON_EXTRACT(json,
'$.customers.name'
)
EMITS (
CUSTOMERS VARCHAR(1000)
)
FROM json_input;
--Error: data exception - more than one SQL/JSON item in column: CUSTOMERSSELECT ID,
JSON_EXTRACT(json,
'$.articles."item number"',
'$.customers.name',
'$.error()'
)
EMITS (
ITEM_NUMBERS VARCHAR(1000),
CUSTOMERS VARCHAR(1000),
ERROR_COLUMN VARCHAR(1000)
)
FROM json_input;Results
| ID | ITEM_NUMBER | CUSTOMERS | ERROR_COLUMN |
| 1 | NULL | NULL | [{ "column" : "ITEM_NUMBERS", "error" : "no SQL/JSON item" }, { "column" : "CUSTOMERS", "error" : "more than one SQL/JSON item" }] |
Error Handling Suffixes
In some cases it is beneficial to specify the error handling for each path separately in JSON_EXTRACT. For that purpose, Exasol provides error handling suffixes. It is possible to append '[NULL|LOG|ERROR] ON EMPTY' and '[NULL|LOG|ERROR] ON ERROR' to any path (except '$.error()') in JSON_EXTRACT. If used on a single path, '[NULL|LOG|ERROR] ON EMPTY' has to be specified before '[NULL|LOG|ERROR] ON ERROR'.
The following table shows the error behavior if json_path_expr points to a non-existing value.
| ON EMPTY Suffix | $.error() column | Example | Result |
|---|---|---|---|
| NONE | NOT PRESENT |
SELECT JSON_EXTRACT('{"name" : "John"}', '$.surname') EMITS (JSON_RESULT VARCHAR(1000)); |
Exception: data exception - no SQL/JSON item |
| ERROR | NOT PRESENT |
SELECT JSON_EXTRACT('{"name" : "John"}', '$.surname ERROR ON EMPTY') EMITS (JSON_RESULT VARCHAR(1000)); |
Exception: data exception - no SQL/JSON item |
| NULL | NOT PRESENT |
SELECT JSON_EXTRACT('{"name" : "John"}', '$.surname NULL ON EMPTY') EMITS (JSON_RESULT VARCHAR(1000)); |
NULL |
| LOG | NOT PRESENT |
SELECT JSON_EXTRACT('{"name" : "John"}', '$.surname LOG ON EMPTY') EMITS (JSON_RESULT VARCHAR(1000)); |
Exception: data exception - invalid JSON path: LOG ON EMPTY used without $.error() column |
| NONE | PRESENT |
SELECT JSON_EXTRACT('{"name" : "John"}', '$.surname', '$.error()') EMITS (JSON_RESULT VARCHAR(1000), ERROR_COLUMN VARCHAR(1000)); |
NULL, ERROR_COLUMN: [{ "column" : "JSON_RESULT", "error" : "no SQL/JSON item" }] |
| ERROR | PRESENT |
SELECT JSON_EXTRACT('{"name" : "John"}', '$.surname ERROR ON EMPTY', '$.error()') EMITS (JSON_RESULT VARCHAR(1000), ERROR_COLUMN VARCHAR(1000)); |
Exception: data exception - no SQL/JSON item |
| NULL | PRESENT |
SELECT JSON_EXTRACT('{"name" : "John"}', '$.surname NULL ON EMPTY', '$.error()') EMITS (JSON_RESULT VARCHAR(1000), ERROR_COLUMN VARCHAR(1000)); |
NULL |
| LOG | PRESENT |
SELECT JSON_EXTRACT('{"name" : "John"}', '$.surname LOG ON EMPTY', '$.error()') EMITS (JSON_RESULT VARCHAR(1000), ERROR_COLUMN VARCHAR(1000)); |
NULL, ERROR_COLUMN: [{ "column" : "JSON_RESULT", "error" : "no SQL/JSON item" }] |
The following table shows the error behavior if the json_path_expr is valid but an error occurs during the JSON processing.
| ON ERROR Suffix | $.error() column | Example | Result |
|---|---|---|---|
| NONE | NOT PRESENT |
SELECT JSON_EXTRACT('{"name" : "John"}', '$.name') EMITS (JSON_RESULT INT); |
Exception: data exception - cast error in column |
| ERROR | NOT PRESENT |
SELECT JSON_EXTRACT('{"name" : "John"}', '$.name ERROR ON ERROR') EMITS (JSON_RESULT INT); |
Exception: data exception - cast error in column |
| NULL | NOT PRESENT |
SELECT JSON_EXTRACT('{"name" : "John"}', '$.name NULL ON ERROR') EMITS (JSON_RESULT INT); |
NULL |
| LOG | NOT PRESENT |
SELECT JSON_EXTRACT('{"name" : "John"}', '$.name LOG ON ERROR') EMITS (JSON_RESULT INT); |
Exception: data exception - invalid JSON path: ERROR ON EMPTY used without $.error() column |
| NONE | PRESENT |
SELECT JSON_EXTRACT('{"name" : "John"}', '$.name', '$.error()') EMITS (JSON_RESULT INT, ERROR_COLUMN VARCHAR(1000)); |
NULL, ERROR_COLUMN: [{ "column" : "JSON_RESULT", "error" : "cast error" }] |
| ERROR | PRESENT |
SELECT JSON_EXTRACT('{"name" : "John"}', '$.name ERROR ON ERROR', '$.error()') EMITS (JSON_RESULT INT, ERROR_COLUMN VARCHAR(1000)); |
Exception: data exception - cast error in column |
| NULL | PRESENT |
SELECT JSON_EXTRACT('{"name" : "John"}', '$.name NULL ON ERROR', '$.error()') EMITS (JSON_RESULT INT, ERROR_COLUMN VARCHAR(1000)); |
NULL |
| LOG | PRESENT |
SELECT JSON_EXTRACT('{"name" : "John"}', '$.name LOG ON ERROR', '$.error()') EMITS (JSON_RESULT INT, ERROR_COLUMN VARCHAR(1000)); |
NULL, ERROR_COLUMN: [{ "column" : "JSON_RESULT", "error" : "cast error" }] |
Examples
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '{"customers" : [{"name" : "Nick"}, {"name" : "Eve"}], "articles" : [{"item number" : 10}, {"item number" : 234}]}');
INSERT INTO json_input VALUES(2, '{"articles" : [{"item number" : 42}]}');
SELECT ID,
JSON_EXTRACT(json,
'$.customers.name LOG ON EMPTY NULL ON ERROR',
'$.articles."item number" NULL ON EMPTY LOG ON ERROR',
'$.error()'
)
EMITS (
CUSTOMERS VARCHAR(1000),
ITEM_NUMBER VARCHAR(1000),
ERROR_COLUMN VARCHAR(1000)
)
FROM json_input;Results
| ID | CUSTOMERS | ITEM_NUMBERS | ERROR_COLUMN |
| 1 | NULL | NULL | [{ "column" : "ITEM_NUMBER", "error" : "more than one SQL/JSON item" }] |
| 2 | NULL | 42 | [{ "column" : "CUSTOMERS", "error" : "no SQL/JSON item" }] |
