[
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[, returnType])}}|{{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.
<pre><code class="language-sql">-- TRUE
VARIANT_GET(
PARSE_JSON('\{"a": true}'),
'$.a',
'BOOLEAN'
)
-- NULL (path does not exist)
VARIANT_GET(
PARSE_JSON('\{"a": true}'),
'$.b',
'INT'
)
-- Throws an error: cannot cast VARIANT(STRING) to INT
VARIANT_GET(
PARSE_JSON('\{"a": "oops"}'),
'$.a',
'INT'
)</code></pre>|
|{{TRY_VARIANT_GET(variant, path[,
returnType])}}|{{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{}}}.
<pre><code class="language-sql">-- TRUE
TRY_VARIANT_GET(
PARSE_JSON('\{"a": true}'),
'$.a',
'BOOLEAN'
)
-- NULL (path does not exist)
TRY_VARIANT_GET(
PARSE_JSON('\{"a": true}'),
'$.b',
'INT'
)
-- NULL (cast fails)
TRY_VARIANT_GET(
PARSE_JSON('\{"a": "oops"}'),
'$.a',
'INT'
)</code></pre>|
h3. References
* [Apache Spark
|https://downloads.apache.org/spark/docs/4.2.0-preview3/api/sql/variant-functions/#try_variant_get]{{[TRY_VARIANT_GET|https://downloads.apache.org/spark/docs/4.2.0-preview3/api/sql/variant-functions/#try_variant_get]}}
* [Snowflake
|https://docs.snowflake.com/en/sql-reference/functions/get]{{[GET|https://docs.snowflake.com/en/sql-reference/functions/get]}}
was:
||SQL Function||Table Function||Description||
|VARIANT_GET(variant, path[, returnType])|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', 'BOOLEAN')}}
{{– NULL (path does not exist)}}
{{VARIANT_GET(PARSE_JSON('\{"a": true}'), '$.b', 'INT')}}
{{– throws: cannot cast VARIANT(STRING) to INTVARIANT_GET(PARSE_JSON('\{"a":
"oops"}'), '$.a', 'INT')}}
{{```\|}}|
|TRY_VARIANT_GET(variant, path[, returnType])|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{}}}.
```sql
– TRUE
VARIANT_GET(PARSE_JSON('\{"a": true}'), '$.a', 'BOOLEAN')
– NULL (path does not exist)
VARIANT_GET(PARSE_JSON('\{"a": true}'), '$.b', 'INT')
– NULL
VARIANT_GET(PARSE_JSON('\{"a": "oops"}'), '$.a', 'INT')
```\||
*reference：*
[https://downloads.apache.org/spark/docs/4.2.0-preview3/api/sql/variant-functions/#try_variant_get]
[https://docs.snowflake.com/en/sql-reference/functions/get]
> Introduce built-in functions `VARIANT_GET` and `TRY_VARIANT_GET ` 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
> Assignee: Jinkun Liu
> Priority: Minor
>
> ||SQL Function||Table Function||Description||
> |{{VARIANT_GET(variant, path[, returnType])}}|{{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.
>
> <pre><code class="language-sql">-- TRUE
> VARIANT_GET(
> PARSE_JSON('\{"a": true}'),
> '$.a',
> 'BOOLEAN'
> )
>
> -- NULL (path does not exist)
> VARIANT_GET(
> PARSE_JSON('\{"a": true}'),
> '$.b',
> 'INT'
> )
>
> -- Throws an error: cannot cast VARIANT(STRING) to INT
> VARIANT_GET(
> PARSE_JSON('\{"a": "oops"}'),
> '$.a',
> 'INT'
> )</code></pre>|
> |{{TRY_VARIANT_GET(variant, path[,
> returnType])}}|{{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{}}}.
>
> <pre><code class="language-sql">-- TRUE
> TRY_VARIANT_GET(
> PARSE_JSON('\{"a": true}'),
> '$.a',
> 'BOOLEAN'
> )
>
> -- NULL (path does not exist)
> TRY_VARIANT_GET(
> PARSE_JSON('\{"a": true}'),
> '$.b',
> 'INT'
> )
>
> -- NULL (cast fails)
> TRY_VARIANT_GET(
> PARSE_JSON('\{"a": "oops"}'),
> '$.a',
> 'INT'
> )</code></pre>|
> h3. References
> * [Apache Spark
> |https://downloads.apache.org/spark/docs/4.2.0-preview3/api/sql/variant-functions/#try_variant_get]{{[TRY_VARIANT_GET|https://downloads.apache.org/spark/docs/4.2.0-preview3/api/sql/variant-functions/#try_variant_get]}}
> * [Snowflake
> |https://docs.snowflake.com/en/sql-reference/functions/get]{{[GET|https://docs.snowflake.com/en/sql-reference/functions/get]}}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
