[FEATURE] JSQLParser 5.1: Table function JSON_TABLE not supported

[newbirdking]

Failing SQL Feature:

• Brief description of the failing SQL feature
• Example: WITH ROLLUP can't be parsed

SQL Example:

SELECT id,knowledge_type_code,knowledge_data,url_routing,keyword,update_by,update_time,sort FROM "SUNYA-SYSTEM".sys_knowledge_base
WHERE ((JSON_UNQUOTE(JSON_EXTRACT(knowledge_data, '$.title')) LIKE '%记%' OR EXISTS (SELECT 1 FROM JSON_TABLE( knowledge_data, '$.drugsList[*]' COLUMNS(drugsName VARCHAR(255) PATH '$.drugsName') ) AS drug WHERE drug.drugsName LIKE '%记%')) AND knowledge_type_code = 'literatureGuidelines') ORDER BY sort ASC;

Software Information:

• JSqlParser version Use the latest version 5.1, 5.0, the first use of 4.4 several versions have tried, still the error is as follows:
• Database (e. g. DM database )

detail error

Exception in thread "main" net.sf.jsqlparser.JSQLParserException: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "EXISTS" "EXISTS"
at line 4, column 78.

Was expecting one of:

"!"
"("
"NOT"

at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:352)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:125)
at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:91)
at com.sunya.system.base.service.impl.SysUserServiceImpl.main(SysUserServiceImpl.java:1090)

Caused by: java.util.concurrent.ExecutionException: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "EXISTS" "EXISTS"
at line 4, column 78.

[manticore-projects]

Greetings.

the table function JSON_TABLE is not supported. Without it, your statement works just fine.

SELECT  id
        , knowledge_type_code
        , knowledge_data
        , url_routing
        , keyword
        , update_by
        , update_time
        , sort
FROM "SUNYA-SYSTEM".sys_knowledge_base
WHERE ( ( Json_Unquote( Json_Extract( knowledge_data, '$.title' ) ) LIKE '%记%'
            OR EXISTS ( SELECT 1
                        FROM dual AS drug
                        WHERE drug.drugsname LIKE '%记%' ) )
        AND knowledge_type_code = 'literatureGuidelines' )
ORDER BY sort ASC
;

[newbirdking]

However, the script you provided failed to execute on the client side. [22000][-2207] There was an error near line 15: member access expression that could not be resolved [drug.drugsname]

[manticore-projects]

However, the script you provided failed to execute on the client side. [22000][-2207] There was an error near line 15: member access expression that could not be resolved [drug.drugsname]

Yes of course, because I replaced your table function and altered the meaning of your query.
It definitely will either not execute or return wrong results because its not the same query.

[newbirdking]

Ok, thank you. Is there any possibility to consider support in the future?

[manticore-projects]

Duplicate #2185