[
https://issues.apache.org/jira/browse/FLINK-39049?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jinkun Liu updated FLINK-39049:
-------------------------------
Description:
||SQL Function||Table Function||Description||
|VARIANT_GET(variant, path RETURNING)|VARIANT.variantGet(variant, path,
returnType])|Extracts a sub-value from a Variant using the specified path and
casts it to the target type.
If the path does not exist or is invalid, this function returns {{{}NULL{}}}.
If the target return type is incompatible with the extracted value, this
function throws an error.
{{```sql}}
{{--TRUE}}
{{VARIANT_GET(PARSE_JSON('\{"a": true}'), '$.a' RETURNING BOOLEAN)}}
{{-- NULL }}
{{VARIANT_GET(PARSE_JSON('\{"a": true}'), '$.b' RETURNING INT)}}
{{-- throws: cannot cast VARIANT(STRING) to INT}}
{{VARIANT_GET(PARSE_JSON('\{"a": "oops"}'), '$.a' RETURNING INT)}}
{{```}}|
|TRY_VARIANT_GET(variant, path RETURNING)|VARIANT.tryVariantGet(variant, path,
returnType])|Similar to {{{}VARIANT_GET{}}}, but returns {{NULL}} instead of
throwing an error when the extracted value cannot be cast to the target return
type.
If the path does not exist or is invalid, this function also returns
{{{}NULL{}}}.|
was:
|SQL Function|Table Function|Description|
|VARIANT_VALUE(variant, path RETURNING type [ \{ NULL \\| ERROR \\| DEFAULT }
ON EMPTY ] [ \{ NULL \| ERROR \| DEFAULT }ON ERROR
])|VARIANT.variantValue(STRING path [, type, onEmpty, defaultOnEmpty, onError,
defaultOnError])|Extracts a scalar as the given type from a variant.
This method queries the variant for the given path expression and returns the
value if the value at that path is scalar. Non-scalar values cannot be returned.
For empty path expressions or errors a behavior can be defined to either return
null, raise an error, or return a defined default value instead. When omitted,
the default is NULL ON EMPTY or NULL ON ERROR, respectively. The default value
may be a literal or an expression. If the default value itself raises an error,
it falls through to the error behavior for ON EMPTY, and raises an error for ON
ERROR.
For path contains special characters such as spaces, you can use ['property']
or ["property"] to select the specified property in a parent object. Be sure to
put single or double quotes around the property name. When using VARIANT_VALUE
in SQL, the path is a character parameter which is already single quoted, so
you have to escape the single quotes around property name, such as JSON_VALUE('
\{"a b": "true"}
', '$.[''a b'']').|
|VARIANT_QUERY(variant, path RETURNING type [ \{ WITHOUT \\| WITH CONDITIONAL
\\| WITH UNCONDITIONAL }[ ARRAY ] WRAPPER ] [{ NULL \\| EMPTY ARRAY \\| EMPTY
OBJECT \\| ERROR } ON EMPTY ] [ \{ NULL \| EMPTY ARRAY \| EMPTY OBJECT \| ERROR
}
ON ERROR ])|VARIANT.variantQuery(path, returnType [, variantQueryWrapper,
variantQueryOnEmptyOrError, variantQueryOnEmptyOrError ])|Extracts values as
type from a variant.
The wrappingBehavior determines whether the extracted value should be wrapped
into an array, and whether to do so unconditionally or only if the value itself
isn’t an array already.
onEmpty and onError determine the behavior in case the path expression is
empty, or in case an error was raised, respectively. By default, in both cases
null is returned. Other choices are to use an empty array, an empty object, or
to raise an error.|
> Introduce built-in functions `VARIANT_VALUE` and `VARIANT_QUERY` to extract
> values from a variant
> -------------------------------------------------------------------------------------------------
>
> Key: FLINK-39049
> URL: https://issues.apache.org/jira/browse/FLINK-39049
> Project: Flink
> Issue Type: Sub-task
> Components: Table SQL / API
> Reporter: Jinkun Liu
> Priority: Major
>
> ||SQL Function||Table Function||Description||
> |VARIANT_GET(variant, path RETURNING)|VARIANT.variantGet(variant, path,
> returnType])|Extracts a sub-value from a Variant using the specified path and
> casts it to the target type.
>
> If the path does not exist or is invalid, this function returns {{{}NULL{}}}.
> If the target return type is incompatible with the extracted value, this
> function throws an error.
>
> {{```sql}}
> {{--TRUE}}
> {{VARIANT_GET(PARSE_JSON('\{"a": true}'), '$.a' RETURNING BOOLEAN)}}
>
> {{-- NULL }}
> {{VARIANT_GET(PARSE_JSON('\{"a": true}'), '$.b' RETURNING INT)}}
>
> {{-- throws: cannot cast VARIANT(STRING) to INT}}
> {{VARIANT_GET(PARSE_JSON('\{"a": "oops"}'), '$.a' RETURNING INT)}}
> {{```}}|
> |TRY_VARIANT_GET(variant, path RETURNING)|VARIANT.tryVariantGet(variant,
> path, returnType])|Similar to {{{}VARIANT_GET{}}}, but returns {{NULL}}
> instead of throwing an error when the extracted value cannot be cast to the
> target return type.
>
> If the path does not exist or is invalid, this function also returns
> {{{}NULL{}}}.|
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
