JSON Path Expressions

This article 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 
SELECT id, JSON_VALUE(json, 'lax $.surname') AS RESULT_VALUE FROM json_input;

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 order

Wildcard 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_NUMBERS
SELECT ID, 
        JSON_EXTRACT(json, 
                 '$.customers.name'
         ) 
         EMITS (
                CUSTOMERS VARCHAR(1000)
         )
FROM json_input;

--Error: data exception - more than one SQL/JSON item in column: CUSTOMERS
SELECT 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" }]