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
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
CREATE TABLE MY_TAB (
my_json VARIANT
);
INSERT INTO MY_TAB SELECT PARSE_JSON('{
"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
-- '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;
Snowflake
-- 'Sayings of the Century'
SELECT JSON_VALUE_UDF(MY_JSON, '$..book[0].title', null, null, null) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'JSON_VALUE_UDF' INSERTED. ***/ 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_UDF(MY_JSON, '$..book[0,1 to 3,3]', null, null, null) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'JSON_VALUE_UDF' INSERTED. ***/ AS VALUE FROM MY_TAB;
-- 'Sayings of the Century'
SELECT JSON_VALUE_UDF(MY_JSON, '$.store.book[*]?(@.category == "reference").title', null, null, null) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'JSON_VALUE_UDF' INSERTED. ***/ AS VALUE FROM MY_TAB;
-- 'MY ERROR MESSAGE'
-- triggers error because the result is a non scalar value (is an object)
SELECT JSON_VALUE_UDF(MY_JSON, '$..book[0]', null, 'MY ERROR MESSAGE'::VARIANT, 'MY EMPTY MESSAGE'::VARIANT) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'JSON_VALUE_UDF' INSERTED. ***/ 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_UDF(MY_JSON, '$..book[0].isbn', null, 'MY ERROR MESSAGE'::VARIANT, 'MY EMPTY MESSAGE'::VARIANT) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'JSON_VALUE_UDF' INSERTED. ***/ AS VALUE FROM MY_TAB;
-- 'NO MATCH FOUND'
-- 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_UDF(MY_JSON, '$..book[0].isbn', null, 'SSC_ERROR_ON_ERROR'::VARIANT, null) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'JSON_VALUE_UDF' INSERTED. ***/ AS VALUE FROM MY_TAB;
-- NULL
SELECT JSON_VALUE_UDF(MY_JSON, '$..book[0].isbn', null, 'SSC_NULL_ON_ERROR'::VARIANT, null) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'JSON_VALUE_UDF' INSERTED. ***/ AS VALUE FROM MY_TAB;
-- 'NO MATCH FOUND'
-- 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_UDF(MY_JSON, '$..book[0].isbn', null, null, 'SSC_ERROR_ON_EMPTY'::VARIANT) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'JSON_VALUE_UDF' INSERTED. ***/ AS VALUE FROM MY_TAB;
-- NULL
SELECT JSON_VALUE_UDF(MY_JSON, '$..book[0].isbn', null, null, 'SSC_NULL_ON_EMPTY'::VARIANT) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'JSON_VALUE_UDF' INSERTED. ***/ AS VALUE FROM MY_TAB;
-- 'Sayings of the Century'
SELECT JSON_VALUE_UDF(MY_JSON, '$..book[0].title', 'string', null, null) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'JSON_VALUE_UDF' INSERTED. ***/ AS VALUE FROM MY_TAB;
-- 'Sayin'
SELECT LEFT(JSON_VALUE_UDF(MY_JSON, '$..book[0].title', 'string', null, null), 5) AS VALUE FROM MY_TAB;
-- 'Sayings of the Century'
SELECT JSON_VALUE_UDF(MY_JSON, '$..book[0].title', 'string', null, null) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'JSON_VALUE_UDF' INSERTED. ***/ AS VALUE FROM MY_TAB;
-- INCORRECT RETURNING TYPE
-- This is because the title field is a string and the function expects a number result type
SELECT JSON_VALUE_UDF(MY_JSON, '$..book[0].title', 'number', null, null) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'JSON_VALUE_UDF' INSERTED. ***/ 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_UDF(MY_JSON, '$..book[0].title', 'number', 420::VARIANT, null) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'JSON_VALUE_UDF' INSERTED. ***/ AS VALUE FROM MY_TAB;
-- 'Sayings of the Century'
SELECT
/*** MSC-WARNING - MSCEWI1021 - 'RETURNING DATE CLAUSE' NOT SUPPORTED ***/
/*JSON_VALUE(MY_JSON, '$..book[0].title' RETURNING DATE ERROR ON ERROR)*/ AS VALUE
FROM MY_TAB;
-- 'Sayings of the Century'
SELECT
/*** MSC-WARNING - MSCEWI1021 - 'ON MISMATCH CLAUSE' NOT SUPPORTED ***/
/*JSON_VALUE(MY_JSON, '$..book[0].title' ERROR ON MISMATCH)*/ AS VALUE
FROM MY_TAB;
Known Issues
1. Returning Type Clause is not fully supported
At the moment, 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
At the moment, 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
MSCEWI1020: Custom UDF inserted.
MSCEWI1021: 'RETURNING <DATATYPE> Clause' is not supported
MSCEWI1021: 'ON MISMATCH Clause' is not supported
Last updated
Was this helpful?