[
https://issues.apache.org/jira/browse/FLINK-39049?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ramin Gharib closed FLINK-39049.
--------------------------------
Fix Version/s: 2.5.0
Release Note: Adds the built-in functions VARIANT_GET(variant, path[,
returnType]) and TRY_VARIANT_GET. They return the value of a VARIANT at a path
such as '$.items[0].qty', cast to a type given as a string literal such as
'INT', or as a VARIANT without one. Paths use 0-based array indices, and a
malformed literal path fails at planning time. A missing field, an index past
the end, or a step on the wrong kind of value returns NULL. TRY_VARIANT_GET
also returns NULL when the value cannot be cast.
Resolution: Fixed
Fixed in master:
- 469332bf2947919049b95982f89e78e35a471a8a
> 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: Ramin Gharib
> Priority: Minor
> Labels: pull-request-available
> Fix For: 2.5.0
>
>
> ||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 an error: cannot cast VARIANT(STRING) to INT
> VARIANT_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
> 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')
> ```|
> 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
(v10.3.26#10030026)
