VasShabu commented on code in PR #28850:
URL: https://github.com/apache/flink/pull/28850#discussion_r3729155633
##########
flink-table/flink-table-runtime/src/main/java/org/apache/flink/table/runtime/functions/SqlJsonUtils.java:
##########
@@ -446,6 +459,107 @@ private static Object dejsonize(String input) {
return JSON_PATH_JSON_PROVIDER.parse(input);
}
+ /**
+ * Returns an upper-case flag describing the type of the parsed JSON
value, mirroring Calcite's
+ * {@code JsonFunctions.jsonType}.
+ *
+ * <p>JSON's grammar cannot express a date, and it has a single number
rule with no width, so
+ * {@code DATE} and {@code FLOAT} cannot be read off the Java type the
parser produces: a date
+ * arrives as a {@link String}, and every non-integral number arrives as a
{@link BigDecimal}
+ * (the shared mapper enables {@code USE_BIG_DECIMAL_FOR_FLOATS}). Both
flags are therefore
+ * inferred from the value itself, which is a deliberate Flink extension
beyond Calcite:
+ *
+ * <ul>
+ * <li>{@code DATE} for a string that is exactly a {@code yyyy-MM-dd}
calendar date, e.g.
+ * {@code "2015-01-01"}, which is the form the JSON format uses to
read a {@code DATE}
+ * column. Nothing else counts. This is stricter than {@code CAST(x
AS DATE)}, which also
+ * coerces {@code 2015-1-1}, {@code 2015-01} and {@code 2015}; those
are
+ * recognition-unsafe here, since {@code "2015"} is a perfectly
ordinary string.
+ * Date-times stay {@code STRING} in every spelling: there is no
timestamp flag to return,
+ * and answering {@code DATE} for a value carrying a time of day
would be worse than
+ * saying nothing. Flink has no single date-time spelling to defer
to either — {@code
+ * CAST}/{@code TO_TIMESTAMP} accept only a space separator, the
JSON format accepts a
+ * space or a {@code T} depending on its {@code
timestamp-format.standard} option, and
+ * variants use {@code T} — so recognition would have to depend on a
per-table option this
+ * function cannot see.
+ * <li>{@code FLOAT} for a number that is exactly representable in 32
bits, e.g. {@code 1.5};
+ * anything needing more precision, such as {@code 11.1}, stays
{@code DOUBLE}.
+ * </ul>
+ *
+ * <p>Note that these flags describe an inferred type that the other JSON
functions do not
+ * share: {@code JSON_VALUE} still returns {@code "2015-01-01"} as a
string.
+ */
+ public static String jsonType(final JsonValueContext parsedInput) {
+ // The parsed context is shared with JSON_VALUE / JSON_QUERY over the
same input, and those
+ // assign it only inside their own args-not-null guard. A NULL path
argument in a preceding
+ // call therefore leaves it null here even though the input itself was
fine. Report NULL
+ // instead of failing, which is how those functions already degrade in
the same situation
+ // (their NPE is swallowed by jsonApiCommonSyntax and falls through to
ON ERROR -> NULL).
+ // A follow-up fixes the sharing for all JSON functions.
+ if (parsedInput == null || parsedInput.hasException()) {
+ return null;
+ }
+ final Object val = parsedInput.obj;
+ if (val instanceof Integer) {
+ return "INTEGER";
+ } else if (val instanceof String) {
+ return isYyyyMmDdDate((String) val) ? "DATE" : "STRING";
+ } else if (val instanceof BigDecimal) {
+ return isExactFloat((BigDecimal) val) ? "FLOAT" : "DOUBLE";
+ } else if (val instanceof Double) {
+ return "DOUBLE";
+ } else if (val instanceof Long || val instanceof BigInteger) {
+ return "LONG";
+ } else if (val instanceof Boolean) {
+ return "BOOLEAN";
+ } else if (val instanceof Map) {
+ return "OBJECT";
+ } else if (val instanceof Collection) {
+ return "ARRAY";
+ } else if (val == null) {
+ return "NULL";
+ }
+ return null;
Review Comment:
Hi Sergey.
Just wanted to consolidate everything and the reason I chose the
implementation that I did.
If I were to return an SQL NULL for input like `json null` we are
essentially grouping a valid json element alongside invalid json
inputs(`garbage`) and the sql NULL input.
My point earlier about snowflake wasnt focusing on the variant type but was
about the fact that snowflake decides to return different values from inputs
(SQL NULL), (JSON 'null').
I think either way we go we can make it work by ensure that users use a util
function like IS JSON before running jsonType, similarly how json_length works
to deal with the ambiguity of the null output.
Essentially I am proposing that the JSON null gets handled by returning
"NULL" and (invalid,sqlNULL) gets handles by returning literal null.
Please let me know you thoughts.
Thanks
Vas.
MariaDB also shares my implementation here:
<img width="1579" height="526" alt="image"
src="https://github.com/user-attachments/assets/09dc2bcf-fffc-4fc9-845c-ba71f0ac243f";
/>
Same with MySQL:
<img width="1606" height="453" alt="image"
src="https://github.com/user-attachments/assets/75d26762-4da9-4261-8846-afe9a635fd36";
/>
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
