JSON_EXTRACT_DOT_NOTATION_UDF

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Definition

User-defined function (UDF) that reproduces dot notation used to querying of values inside JSON objects.

JSON_EXTRACT_DOT_NOTATION_UDF(JSON_OBJECT VARIANT, JSON_PATH STRING)

Differences between Teradata JSON Entity Reference (dot notation ) and Snowflake JSON query method.

Teradata and Snowflake differ in the methods used for traverse JSON data. In this case Teradata use a way based on JavaScript that use dot notation, array indexing, and other especial operators like wildcard access or double dot. On the other hand, snowflake doesn't support these operators, and it only can access members and arrays.

Parameters

JSON_OBJECT VARIANT

The JSON variable from which to extract the values.

JSON_PATH STRING

The JSON path that indicates where the values are located inside the JSON_OBJECT.

Returns

The values specified by the JSON_PATH inside the JSON_OBJECT.

Migration example

Input:

IN -> Teradata_01.sql

SELECT CAST(varcharColumn AS JSON(2000))..name FROM variantTest;

Output:

OUT -> Teradata_01.sql

SELECT
JSON_EXTRACT_DOT_NOTATION_UDF(CAST(varcharColumn AS VARIANT), '$..name')
FROM
variantTest;