This section describes the elements from the SQL/JSON path language that are supported in Exasol.
The parts of the SQL/JSON path language that are not mentioned in this section are not supported.
JSON path modes and prefixes
The SQL/JSON path language supports both strict and lax JSON syntax using the prefixes strict and lax.
Exasol only supports the lax prefix. The lax prefix can be added to any path in Exasol, but it has no effect on the query result.
Example:
CREATE OR REPLACE TABLE json_input(id INT, json VARCHAR(2000000));
INSERT INTO json_input VALUES(1, '{"firstname" : "Claire", "lastname" : "Doe"}');
INSERT INTO json_input VALUES(2, '{"firstname" : "John", "age" : 20}');
// SELECT statement 1
SELECT id, JSON_VALUE(json, '$.lastname') AS RESULT_VALUE FROM json_input;
// SELECT statement 2
SELECT id, JSON_VALUE(json, 'lax $.lastname') AS RESULT_VALUE FROM json_input;Both SELECT statements will return the same result:
| ID | RESULT_VALUE |
| 1 | Doe |
| 2 | NULL |
Variables
The SQL/JSON path language supports several variables.
Exasol supports arbitrary expressions such as json_path_expr, which means it does not have to support the PASSING clause and JSONpath named variables.
The '@' variable is not supported in Exasol.
JSONpath context variable
The context variable '$' is the root element in a JSON path.
Example:
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 |
Accessors
Accessors are used to navigate through a JSON structure.
Member accessor
A member accessor gives access to a JSON object member by its name. You can concatenate an arbitrary number of names to access nested objects.
The member accessor is case sensitive. If a name starts with the character $, or if the name contains special characters (including spaces), you must enclose the name with double quotes.
The member accessor adheres to the lexical grammar of the ECMAScript language.
Example:
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, '{"First name" : "Luke" }');
INSERT INTO json_input VALUES(5, '{"$firstname" : "Sue" }');
INSERT INTO json_input VALUES(6, '{"Full name" : "John Doe" }');
SELECT id,
JSON_VALUE(json, '$.customer."first name"') AS CUSTOMER_FIRST_NAME,
JSON_VALUE(json, '$."first name"') AS "first name",
JSON_VALUE(json, '$.First name') AS "First name",
JSON_VALUE(json, '$."$firstname"') AS "$firstname",
JSON_VALUE(json, '$."Full name"') AS "Full name"
FROM json_input;Result:
| ID | CUSTOMER_FIRST_NAME | first name | First name | $firstname | Full name |
| 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
You can use the wildcard member accessor * to select all JSON object members in the path.
Example:
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", "last name" : "Doe" } }');
SELECT id,
JSON_VALUE(json, '$.*.first name') AS FIRST_NAME,
JSON_VALUE(json, '$.*.*') AS DOUBLE_ASTERISK,
JSON_VALUE(json, '$.*.last name') AS LAST_NAME
FROM json_input;Result:
| ID | FIRST_NAME | DOUBLE_ASTERISK | LAST_NAME |
| 1 | Ann | Ann | NULL |
| 2 | John | John | NULL |
| 3 | Ann | NULL | Doe |
Element accessor
You can use an element accessor to select one or more indexes from a JSON array.
Exasol supports access to single elements as well as ranges. You can also combine single element access with range access. However, Exasol does not support overlapping or unordered indexes.
Example 1
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 |
Example 2
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.
Because Exasol always uses lax path mode (see JSON path modes and prefixes), you do not have to specify the wildcard element accessor for a single array, only for nested arrays. If the wildcard element accessor is applied to a JSON value that is not an array, the lax mode automatically wraps the value into an array.
Example:
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 |
Functions
You can call functions on an SQL/JSON value defined by a path. Exasol supports the functions type() and size().
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()
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", "last name" : "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", "last name" : "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 |
Exasol extensions
For manual exploration of JSON data and for debugging purposes, Exasol provides several extensions to the SQL/JSON path language. The extensions in this category are specific to Exasol and not part of the SQL standard.
Function json()
Converts a JSON value (or values) specified by a path prefix into a JSON string.
A possible application is the extraction of complete JSON objects or JSON arrays as (VAR)CHAR.
The json() function removes whitespace characters between JSON elements from the original JSON input.
For additional information, see also JSON_VALUE.
Example:
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()
Returns the actual path that Exasol uses as (VAR)CHAR. This can help to debug paths with wildcards.
Example:
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;
Result:
| 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()
Determines the actual path that Exasol uses and splits it into an array of accessors.
The function returns a (VAR)CHAR result that contains a JSON array with the identified accessors of the path.
Example:
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;Result:
| 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()
Returns the name of a name/value pair defined in a JSON object.
Example:
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;Result:
| ID | JSON_NAME | NESTED_JSON_NAME | NESTED_JSON_VALUE |
| 1 | customers | first name | John |
| 2 | telephone list | number | +49123456789 |
Function id()
Prints a unique identifier for each JSON element. The identifier is only unique within one JSON input.
Example:
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"}, {"last name" : "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;Result:
| 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()
Returns the position of an element within an array.
The index of the first element in an array is 0 (zero).
For elements that are not part of an array, the function also returns 0.
This can help in identifying specific data elements.
Example 1
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 FIRST_NAME_POSITION,
JSON_VALUE(json, '$.position()') AS CONTEXT_POSITION
FROM json_input;Result:
| ID | POSITION_1 | FIRST_NAME_POSITION | CONTEXT_POSITION |
| 1 | 1 | 0 | 0 |
| 2 | NULL | NULL | 0 |
| 3 | NULL | NULL | 0 |
Example 2
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;Result:
| ID | JSON_POSITION | NAME |
| 1 | 0 | Nick |
| 1 | 1 | Eve |
| 2 | 0 | Ann |
| 2 | 1 | John |
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.
Example:
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;Result:
| 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
You can use this accessor to select all leaf values of a JSON input at once.
Example:
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;Result:
| 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] |
JSON_EXTRACT extensions
JSON_EXTRACT is an Exasol-specific function that converts JSON into a relational format during ETL jobs.
For this purpose, there are several path extensions that are only available in JSON_EXTRACT.
For more details, see JSON_EXTRACT.
Extract multiple rows from nested data
You can use JSON_EXTRACT 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 by using 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.
Example 1
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;Result:
| 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 |
Example 2
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;Result:
| ID | GENERATED_ID | NAME |
| 1 | 4 | Ann |
| 1 | 8 | Michael |
| 2 | 5 | Eve |
| 2 | 8 | Joe |
| 2 | 13 | Sven |
Example 3
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.
You can change this behavior to LOG ON EMPTY and LOG ON ERROR by adding the special path $.error() as the 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
To specify separate error handling by JSON_EXTRACT for each JSON path, you can use error handling suffixes.
You can append [NULL|LOG|ERROR] ON EMPTY and [NULL|LOG|ERROR] ON ERROR to any path in JSON_EXTRACT except $.error().
If used on a single path, [NULL|LOG|ERROR] ON EMPTY has to be specified before [NULL|LOG|ERROR] ON ERROR.
For more details, see JSON Error Handling.
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"}', '$.lastname') EMITS (JSON_RESULT VARCHAR(1000)); |
Exception: data exception - no SQL/JSON item |
| ERROR | NOT PRESENT |
SELECT JSON_EXTRACT('{"name" : "John"}', '$.lastnameERROR ON EMPTY') EMITS (JSON_RESULT VARCHAR(1000)); |
Exception: data exception - no SQL/JSON item |
| NULL | NOT PRESENT |
SELECT JSON_EXTRACT('{"name" : "John"}', '$.lastnameNULL ON EMPTY') EMITS (JSON_RESULT VARCHAR(1000)); |
NULL |
| LOG | NOT PRESENT |
SELECT JSON_EXTRACT('{"name" : "John"}', '$.lastnameLOG 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"}', '$.lastname', '$.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"}', '$.lastnameERROR 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"}', '$.lastnameNULL ON EMPTY', '$.error()') EMITS (JSON_RESULT VARCHAR(1000), ERROR_COLUMN VARCHAR(1000)); |
NULL |
| LOG | PRESENT |
SELECT JSON_EXTRACT('{"name" : "John"}', '$.lastnameLOG 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" }] |
Example:
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;Result:
| 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" }] |
