[jira] [Commented] (FLINK-9477) FLIP-90: Support SQL 2016 JSON functions in Flink SQL

["Jark Wu (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/FLINK-9477?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17267609#comment-17267609 ] 

Jark Wu commented on FLINK-9477:
--------------------------------

I think JSON function is an important long-missing feature and we should support this in the next release. 

But I reconsider the API again, and have some thoughts.

Currentlly, all the json path string requires the {{lax}} or {{strict}} prefix, e.g. {{JSON_VALUE(json, 'lax $.foo')}}.

Strict mode is used to examine data from a strict schema perspective, for example, to look for data that diverges from an expected schema. Therefore, strict mode raises an error if the data does not strictly adhere to the requirements of a path expression. Lax mode is intended to be more forgiving, so lax mode converts errors to empty JSON sequences. 

However, Flink as a bigdata streaming processing system, an long-running service, the job shouldn't fail even dirty data arrives. Even if we want to examine dirty data, this should be a job level configuration and take effect to all functions. Therefore, we don't support "strict" mode and users have to write "lax" prefix, this sounds not useful. Why not eliminate the "lax" prefix? 

I also investivate other databases/systems, all of them don't follow the SQL standard, and only use the json path string without "lax" or "strict" prefix, e.g. MySQL[1], Oracle[2], SqlServer[3], Trino(Presto)[4].

Thus, I suggest to support the json path without "lax" or "strict" prefix and converts errors to empty or null json sequences. 

What do you think ? [~x1q1j1] [~lzljs3620320] [~docete] [~danny0405] (the FLIP voters).


[1]: https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-extract
[2]: https://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6263
[3]: https://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql?view=sql-server-2017
[4]: https://trino.io/docs/current/functions/json.html#json-functions

> FLIP-90: Support SQL 2016 JSON functions in Flink SQL
> -----------------------------------------------------
>
>                 Key: FLINK-9477
>                 URL: https://issues.apache.org/jira/browse/FLINK-9477
>             Project: Flink
>          Issue Type: New Feature
>          Components: Table SQL / API
>            Reporter: Shuyi Chen
>            Assignee: Forward Xu
>            Priority: Major
>             Fix For: 1.13.0
>
>
> FLIP Link https://cwiki.apache.org/confluence/pages/viewpage.action?pageId=141724550



--
This message was sent by Atlassian Jira
(v8.3.4#803005)