JSON_EXTRACT_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 JSONExtract, JSONExtractValue, and JSONExtractLargeValue functions to extract multiple values out of a JSON variable.

JSON_EXTRACT_UDF(JSON_OBJECT VARIANT, JSON_PATH STRING, SINGLE_VALUE BOOLEAN)

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.

SINGLE_VALUE BOOLEAN

BOOLEAN if true, it returns only one value (necessary for JSONExtractValue AND JSONExtractLargeValue), otherwise returns an array (JSONExtract).

Returns

The values specified by the JSON_PATH inside the JSON_OBJECT.

Migration example

Input:

IN -> Teradata_01.sql

SELECT
    Store.JSONExtract('$..author') as AllAuthors
FROM BookStores;

Output:

OUT -> Teradata_01.sql

SELECT
    JSON_EXTRACT_UDF(Store, '$..author', FALSE) as AllAuthors
    FROM
    BookStores;