JSON_VALUE UDF

Translation reference to convert Oracle JSON_VALUE function to Snowflake

Description

As per Oracle's documentation, this function uses the SQL/JSON Path Expression to request information about a portion of a JSON instance. The returning value is always a scalar value, else the function returns NULL by default.

JSON_VALUE
  ( expr [ FORMAT JSON ], [ JSON_basic_path_expression ]
    [ JSON_value_returning_clause ] [ JSON_value_on_error_clause ]
    [ JSON_value_on_empty_clause ][ JSON_value_on_mismatch_clause ]
  )

The JSON_VALUE_UDF is a Snowflake implementation of the JSONPath specification that uses a modified version of the original JavaScript implementation developed by Stefan Goessner.

Sample Source Patterns

Setup Data

Run these queries to run queries in the JSON_VALUE Patterns section.

Oracle

Query

IN -> Oracle_01.sql

CREATE TABLE MY_TAB (
    my_json VARCHAR(5000)
);

INSERT INTO MY_TAB VALUES ('{ 
    "store": {
      "book": [ 
        { "category": "reference",
          "author": "Nigel Rees",
          "title": "Sayings of the Century",
          "price": 8.95
        },
        { "category": "fiction",
          "author": "Evelyn Waugh",
          "title": "Sword of Honour",
          "price": 12.99
        },
        { "category": "fiction",
          "author": "Herman Melville",
          "title": "Moby Dick",
          "isbn": "0-553-21311-3",
          "price": 8.99
        },
        { "category": "fiction",
          "author": "J. R. R. Tolkien",
          "title": "The Lord of the Rings",
          "isbn": "0-395-19395-8",
          "price": 22.99
        }
      ],
      "bicycle": {
        "color": "red",
        "price": 19.95
      }
    }
  }');

Snowflake

Query

OUT -> Oracle_01.sql

CREATE OR REPLACE TABLE MY_TAB (
       my_json VARCHAR(5000)
   )
   COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}'
   ;

   INSERT INTO MY_TAB
   VALUES ('{ 
    "store": {
      "book": [ 
        { "category": "reference",
          "author": "Nigel Rees",
          "title": "Sayings of the Century",
          "price": 8.95
        },
        { "category": "fiction",
          "author": "Evelyn Waugh",
          "title": "Sword of Honour",
          "price": 12.99
        },
        { "category": "fiction",
          "author": "Herman Melville",
          "title": "Moby Dick",
          "isbn": "0-553-21311-3",
          "price": 8.99
        },
        { "category": "fiction",
          "author": "J. R. R. Tolkien",
          "title": "The Lord of the Rings",
          "isbn": "0-395-19395-8",
          "price": 22.99
        }
      ],
      "bicycle": {
        "color": "red",
        "price": 19.95
      }
    }
  }');

JSON_VALUE Patterns

Oracle

Query

IN -> Oracle_02.sql

-- 'Sayings of the Century'
SELECT JSON_VALUE(MY_JSON, '$..book[0].title') AS VALUE FROM MY_TAB;

-- NULL
-- gets books in positions 0, 1, 2 and 3 but returns null (default behavior) since a non scalar value was returned
SELECT JSON_VALUE(MY_JSON, '$..book[0,1 to 3,3]') AS VALUE FROM MY_TAB;

-- 'Sayings of the Century'
SELECT JSON_VALUE(MY_JSON, '$.store.book[*]?(@.category == "reference").title') AS VALUE FROM MY_TAB;

-- 'MY ERROR MESSAGE'
-- triggers error because the result is a non scalar value (is an object)
SELECT JSON_VALUE(MY_JSON, '$..book[0]' DEFAULT 'MY ERROR MESSAGE' ON ERROR DEFAULT 'MY EMPTY MESSAGE' ON EMPTY) AS VALUE FROM MY_TAB;

-- 'MY EMPTY MESSAGE'
-- triggers the on empty class because does not exists in the first book element
SELECT JSON_VALUE(MY_JSON, '$..book[0].isbn' DEFAULT 'MY ERROR MESSAGE' ON ERROR DEFAULT 'MY EMPTY MESSAGE' ON EMPTY) AS VALUE FROM MY_TAB;

-- Oracle error message: ORA-40462: JSON_VALUE evaluated to no value
-- this is a custom message from the UDF when no match is found and the ON ERROR clause is set to ERROR
SELECT JSON_VALUE(MY_JSON, '$..book[0].isbn' ERROR ON ERROR) AS VALUE FROM MY_TAB;

-- NULL
SELECT JSON_VALUE(MY_JSON, '$..book[0].isbn' NULL ON ERROR) AS VALUE FROM MY_TAB;

-- Oracle error message: ORA-40462: JSON_VALUE evaluated to no value
-- this is a custom message from the UDF when no match is found and the ON EMPTY clause is set to ERROR
SELECT JSON_VALUE(MY_JSON, '$..book[0].isbn' ERROR ON EMPTY) AS VALUE FROM MY_TAB;

-- NULL
SELECT JSON_VALUE(MY_JSON, '$..book[0].isbn' NULL ON EMPTY) AS VALUE FROM MY_TAB;

-- 'Sayings of the Century'
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' RETURNING VARCHAR2) AS VALUE FROM MY_TAB;

-- 'Sayin'
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' RETURNING VARCHAR2(5) TRUNCATE) AS VALUE FROM MY_TAB;

-- 'Sayings of the Century'
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' RETURNING CLOB) AS VALUE FROM MY_TAB;

-- NULL
-- This is because the title field is a string and the function expects a number result type
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' RETURNING NUMBER) AS VALUE FROM MY_TAB;

-- 420
-- This is because the title field is a string and the function expects a number result type
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' RETURNING NUMBER DEFAULT 420 ON ERROR) AS VALUE FROM MY_TAB;

-- Oracle error message: ORA-01858: a non-numeric character was found where a numeric was expected
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' RETURNING DATE ERROR ON ERROR) AS VALUE FROM MY_TAB;

-- ORA-40450: invalid ON ERROR clause
SELECT JSON_VALUE(MY_JSON, '$..book[0].title' ERROR ON MISMATCH) AS VALUE FROM MY_TAB;

Results

JSON Path	Query result
'$..book[0].title'	'Sayings of the Century'
'$..book[0,1 to 3,3]'	NULL
'$.store.book[*]?(@.category == "reference").title'	'Sayings of the Century'
'$..book[0]'	'MY ERROR MESSAGE'
'$..book[0].isbn'	'MY EMPTY MESSAGE'
'$..book[0].isbn'	ORA-40462: JSON_VALUE evaluated to no value
'$..book[0].isbn'	NULL
'$..book[0].isbn'	ORA-40462: JSON_VALUE evaluated to no value
'$..book[0].isbn'	NULL
'$..book[0].title'	'Sayings of the Century'
'$..book[0].title'	'Sayin'
'$..book[0].title'	'Sayings of the Century'
'$..book[0].title'	NULL
'$..book[0].title'	420
'$..book[0].title'	ORA-01858: a non-numeric character was found where a numeric was expected
'$..book[0].title'	ORA-40450: invalid ON ERROR clause

Snowflake

Query

OUT -> Oracle_02.sql

-- 'Sayings of the Century'
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "MY_TAB" **
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', NULL, NULL, NULL) AS VALUE FROM
MY_TAB;

-- NULL
-- gets books in positions 0, 1, 2 and 3 but returns null (default behavior) since a non scalar value was returned
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "MY_TAB" **
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0,1 to 3,3]', NULL, NULL, NULL) AS VALUE FROM
MY_TAB;

-- 'Sayings of the Century'
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "MY_TAB" **
SELECT
JSON_VALUE_UDF(MY_JSON, '$.store.book[*]?(@.category == "reference").title', NULL, NULL, NULL) AS VALUE FROM
MY_TAB;

-- 'MY ERROR MESSAGE'
-- triggers error because the result is a non scalar value (is an object)
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "MY_TAB" **
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0]', NULL, 'MY ERROR MESSAGE' :: VARIANT, 'MY EMPTY MESSAGE' :: VARIANT) AS VALUE FROM
MY_TAB;

-- 'MY EMPTY MESSAGE'
-- triggers the on empty class because does not exists in the first book element
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "MY_TAB" **
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].isbn', NULL, 'MY ERROR MESSAGE' :: VARIANT, 'MY EMPTY MESSAGE' :: VARIANT) AS VALUE FROM
MY_TAB;

-- Oracle error message: ORA-40462: JSON_VALUE evaluated to no value
-- this is a custom message from the UDF when no match is found and the ON ERROR clause is set to ERROR
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "MY_TAB" **
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].isbn', NULL, 'SSC_ERROR_ON_ERROR' :: VARIANT, NULL) AS VALUE FROM
MY_TAB;

-- NULL
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "MY_TAB" **
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].isbn', NULL, 'SSC_NULL_ON_ERROR' :: VARIANT, NULL) AS VALUE FROM
MY_TAB;

-- Oracle error message: ORA-40462: JSON_VALUE evaluated to no value
-- this is a custom message from the UDF when no match is found and the ON EMPTY clause is set to ERROR
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "MY_TAB" **
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].isbn', NULL, NULL, 'SSC_ERROR_ON_EMPTY' :: VARIANT) AS VALUE FROM
MY_TAB;

-- NULL
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "MY_TAB" **
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].isbn', NULL, NULL, 'SSC_NULL_ON_EMPTY' :: VARIANT) AS VALUE FROM
MY_TAB;

-- 'Sayings of the Century'
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "MY_TAB" **
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', 'string', NULL, NULL) AS VALUE FROM
MY_TAB;

-- 'Sayin'
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "MY_TAB" **
SELECT
LEFT(JSON_VALUE_UDF(MY_JSON, '$..book[0].title', 'string', NULL, NULL), 5) AS VALUE FROM
MY_TAB;

-- 'Sayings of the Century'
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "MY_TAB" **
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', 'string', NULL, NULL) AS VALUE FROM
MY_TAB;

-- NULL
-- This is because the title field is a string and the function expects a number result type
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "MY_TAB" **
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', 'number', NULL, NULL) AS VALUE FROM
MY_TAB;

-- 420
-- This is because the title field is a string and the function expects a number result type
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "MY_TAB" **
SELECT
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', 'number', 420 :: VARIANT, NULL) AS VALUE FROM
MY_TAB;

-- Oracle error message: ORA-01858: a non-numeric character was found where a numeric was expected
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "MY_TAB" **
SELECT
--** SSC-FDM-0028 - RETURNING CLAUSE NOT SUPPORTED IN SNOWFLAKE **
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', NULL, 'SSC_ERROR_ON_ERROR' :: VARIANT, NULL) AS VALUE FROM
MY_TAB;

-- ORA-40450: invalid ON ERROR clause
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT "MY_TAB" **
SELECT
--** SSC-FDM-0028 - ON MISMATCH CLAUSE NOT SUPPORTED IN SNOWFLAKE **
JSON_VALUE_UDF(MY_JSON, '$..book[0].title', NULL, NULL, NULL) AS VALUE FROM
MY_TAB;

Results

JSON Path	Query result
'$..book[0].title'	'Sayings of the Century'
'$..book[0,1 to 3,3]'	NULL
'$.store.book[*]?(@.category == "reference").title'	'Sayings of the Century'
'$..book[0]'	'MY ERROR MESSAGE'
'$..book[0].isbn'	'MY EMPTY MESSAGE'
'$..book[0].isbn'	"SSC_CUSTOM_ERROR - NO MATCH FOUND"
'$..book[0].isbn'	NULL
'$..book[0].isbn'	"SSC_CUSTOM_ERROR - NO MATCH FOUND"
'$..book[0].isbn'	NULL
'$..book[0].title'	'Sayings of the Century'
'$..book[0].title'	'Sayin'
'$..book[0].title'	'Sayings of the Century'
'$..book[0].title'	NULL
'$..book[0].title'	420
'$..book[0].title'	NOT SUPPORTED
'$..book[0].title'	NOT SUPPORTED

Known Issues

1. Returning Type Clause is not fully supported

Now, the only supported types when translating the functionality of the RETURNING TYPE clause are VARCHAR2, CLOB and NUMBER.

For all the other types supported by the original JSON_VALUE function, the JSON_VALUE_UDF will behave as if no RETURNING TYPE clause was specified.

Unsupported types:

• DATE

• TIMESTAMP [WITH TIME ZONE]

• SDO_GEOMETRY

• CUSTOM TYPE

2. ON MISMATCH Clause is not supported

Now, the ON MISMATCH clause is not supported, and a warning EWI is placed instead. Thus, the translated code will behave as if no ON MISMATCH clause was originally specified.

3. Complex filters are not supported

Complex filters with more than one expression will return null as they are not supported.

For example, with the same data as before, this JSON path $.store.book[*]?(@.category == "reference").title is supported and will return 'Sayings of the Century'.

However, $.store.book[*]?(@.category == "reference" && @.price < 10).title will return null since more than one expression is used in the filter.

Related EWIs

1. SSC-FDM-0007: Element with missing dependencies.

2. SSC-FDM-0028: Syntax not supported in Snowflake.