JSON_EXTRACT_UDF

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

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:

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

Output:

SELECT
    JSON_EXTRACT_UDF(Store, '$..author', FALSE) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'JSON_EXTRACT_UDF' INSERTED. ***/ as AllAuthors
    FROM
    BookStores;