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

  1. MSCEWI1020: Custom UDF inserted.

  2. MSCEWI1021: 'RETURNING <DATATYPE> Clause' is not supported

  3. MSCEWI1021: 'ON MISMATCH Clause' is not supported

PreviousJSDATE UDFNextJULIAN TO GREGORIAN DATE UDF

Last updated