JSON_EXTRACT_DOT_NOTATION_UDF

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;