[jira] [Created] (FLINK-40463) Widen JSON_VALUE and JSON_QUERY RETURNING type support

["Chris Johnson (Jira)" <ji...@apache.org>]

Chris Johnson created FLINK-40463:
-------------------------------------

             Summary: Widen JSON_VALUE and JSON_QUERY RETURNING type support
                 Key: FLINK-40463
                 URL: https://issues.apache.org/jira/browse/FLINK-40463
             Project: Flink
          Issue Type: Improvement
          Components: Table SQL / Planner
            Reporter: Chris Johnson


JSON_VALUE and JSON_QUERY both let users specify a return type with the RETURNING clause, but many common types are not supported.

JSON_VALUE only allows VARCHAR, BOOLEAN, INTEGER, and DOUBLE as return types. It should also support TINYINT, SMALLINT, BIGINT, FLOAT, and DECIMAL.

There are also two existing bugs in how INTEGER and DOUBLE results are converted. The code assumes the JSON library always returns the exact Java type it expects, but that is not the case -- for example, large numbers come back as Long instead of Integer, which causes a runtime error. The fix is to convert through java.lang.Number, which handles all numeric types correctly.

JSON_QUERY only allows RETURNING ARRAY<VARCHAR>. Other array types like ARRAY<INT> or ARRAY<BOOLEAN> are rejected, even though the underlying JSON data is perfectly valid. This forces users to extract as ARRAY<VARCHAR> and then convert each element themselves.

This ticket covers:
 # Adding the missing numeric return types to JSON_VALUE, and fixing the INTEGER/DOUBLE conversion bugs
 # Adding ARRAY<BOOLEAN> and ARRAY<numeric> support to JSON_QUERY
 # Fixing a copy-paste error where a JSON_QUERY error message incorrectly says "JSON_VALUE"



--
This message was sent by Atlassian Jira
(v8.20.10#820010)