JSON_EXTRACT_UDF

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;