JSON_VALUE_UDF

Definition

This user-defined function (UDF) reproduces the JSON_VALUE function to extract a single result out of a JSON variable.

JSON_VALUE_UDF(JSON_OBJECT VARIANT, JSON_PATH STRING, RETURNING_TYPE STRING, ON_ERROR_MESSAGE VARIANT, ON_EMPTY_MESSAGE VARIANT)

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.

RETURNING_TYPE STRING

The type to return.

ON_ERROR_MESSAGE VARIANT

The error message to add if needed.

ON_EMPTY_MESSAGE VARIANT

The error message to add in case of empty message.

Returns

Returns a single value specified by the JSON_PATH inside the JSON_OBJECT. If the result is not a single value, returns a default error message or an error message defined in the input parameters.

Usage example

Input:

   SELECT
     JSON_VALUE_UDF(
     
     PARSE_JSON('{
  "iceCreamOrders": [
    {
      "customerID": "CUST001",
      "orderID": "ORD001",
      "productID": "PROD001",
      "quantity": 2
    }
  ]
}'),

JSON_EXTRACT_PATH_TEXT('{
  "iceCreamOrders": [
    {
      "customerID": "CUST001",
      "orderID": "ORD001",
      "productID": "PROD001",
      "quantity": 2
    }
  ]
}', 'iceCreamOrders'), 'VARIANT', TO_VARIANT('There was an error'), TO_VARIANT('Empty message'));

Output:

"Empty message"