Re: [PR] [FLINK-40268][table] Add new JSON_TYPE function [flink]

["gustavodemorais (via GitHub)" <gi...@apache.org>]

gustavodemorais commented on PR #28850:
URL: https://github.com/apache/flink/pull/28850#issuecomment-5144513892

   General feedback after the first pass
   
   
   JSON itself only knows six kinds of value: object, array, string, number, boolean, and null. That's it - there's no such thing as a "date" or a "float" in JSON. So if someone writes {"d": "2015-01-01"}, that d is a string that happens to look like a date. The PR guesses and reports DATE, and similarly guesses FLOAT vs DOUBLE based on how precise the number is. Our recommendation is not to guess: just report what's actually there.
   
   Other databases sometimes do report DATE, which is why this looks like precedent - but they can only do it because they store JSON in their own typed format, so a real date got written in as a real date. Flink is handed a plain string of text, which carries no such information. And it matters practically: if you write CASE JSON_TYPE(x) WHEN 'STRING' THEN ..., every date-looking string silently skips your branch. Reporting the six real types keeps the answer both true, predictable and useful. We want to implement this in a way it's useful for typical use cases: users reach for it to branch on fields whose shape varies - a payload where errors is sometimes a string, sometimes an array. For that, following the json native way is a simpler and sufficient.
   
   That said, some concrete suggestions:
   
   - Add an optional path: JSON_TYPE(json [, path]).
   - Cut to JSON's six types: OBJECT ARRAY STRING NUMBER BOOLEAN 'NULL'. The input is a VARCHAR; it carries nothing more.
   - Drop DATE and FLOAT - that's sniffing, not typing, and no engine does it. 
   - Fold INTEGER/LONG into NUMBER
   - Also, think about VARIANT story - TYPEOF(PARSE_JSON(...)) returns 'VARIANT', so we risk two vocabularies for one question
   


-- 
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: issues-unsubscribe@flink.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org