Repository navigation
Cannot parse PGSQL JSONB_ARRAY_ELEMENTS() WITH ORDINALITY ARR() #1511
Description
Was expecting one of:
"."
";"
"ACTION"
"ACTIVE"
"ALGORITHM"
"ARCHIVE"
"ARRAY"
"AS"
"AT"
"BYTE"
"CASCADE"
"CASE"
"CAST"
"CHANGE"
"CHAR"
"CHARACTER"
"CHECKPOINT"
"COLUMN"
"COLUMNS"
"COMMENT"
"COMMIT"
"CONNECT"
"COSTS"
"CYCLE"
"DBA_RECYCLEBIN"
"DEFAULT"
"DESC"
"DESCRIBE"
"DISABLE"
"DISCONNECT"
"DIV"
"DO"
"DUMP"
"DUPLICATE"
"EMIT"
"ENABLE"
"END"
"EXCLUDE"
"EXTRACT"
"FALSE"
"FILTER"
"FIRST"
"FLUSH"
"FN"
"FOLLOWING"
"FORMAT"
"FULLTEXT"
"GROUP"
"HAVING"
"HISTORY"
"INDEX"
"INSERT"
"INTERVAL"
"ISNULL"
"JSON"
"KEY"
"LAST"
"LEADING"
"LINK"
"LOCAL"
"LOG"
"MATERIALIZED"
"NO"
"NOLOCK"
"NULLS"
"OF"
"OPEN"
"OVER"
"PARALLEL"
"PARTITION"
"PATH"
"PERCENT"
"PIVOT"
"PRECISION"
"PRIMARY"
"PRIOR"
"QUERY"
"QUIESCE"
"RANGE"
"READ"
"RECYCLEBIN"
"REGISTER"
"REPLACE"
"RESTRICTED"
"RESUME"
"ROW"
"ROWS"
"SCHEMA"
"SEPARATOR"
"SEQUENCE"
"SESSION"
"SHUTDOWN"
"SIBLINGS"
"SIGNED"
"SIZE"
"SKIP"
"START"
"SUSPEND"
"SWITCH"
"SYNONYM"
"SYSTEM"
"TABLE"
"TABLESPACE"
"TEMP"
"TEMPORARY"
"TIMEOUT"
"TO"
"TOP"
"TRUE"
"TRUNCATE"
"TRY_CAST"
"TYPE"
"UNQIESCE"
"UNSIGNED"
"USER"
"VALIDATE"
"VALUE"
"VALUES"
"VIEW"
"WINDOW"
"XML"
"ZONE"
<EOF>
<K_DATETIMELITERAL>
<K_DATE_LITERAL>
<K_NEXTVAL>
<K_STRING_FUNCTION_NAME>
<S_CHAR_LITERAL>
<S_IDENTIFIER>
<S_QUOTED_IDENTIFIER>
at net.sf.jsqlparser.parser.CCJSqlParserManager.parse(CCJSqlParserManager.java:25) ~[jsqlparser-4.4.jar!/:?]
at com.iwhalecloud.interfaces.common.dao.BaseDAO.propNameForQryColStr(BaseDAO.java:478) ~[classes!/:0.0.1]
at com.iwhalecloud.interfaces.common.dao.BaseDAO.queryForList(BaseDAO.java:351) ~[classes!/:0.0.1]
at com.iwhalecloud.interfaces.common.dao.CommonDAO.queryList(CommonDAO.java:283) ~[classes!/:0.0.1]
at com.iwhalecloud.interfaces.common.helper.CommonHelper.executeSQL(CommonHelper.java:1487) ~[classes!/:0.0.1]
at com.iwhalecloud.interfaces.common.helper.CommonHelper.executeSQL(CommonHelper.java:1473) ~[classes!/:0.0.1]
at com.iwhalecloud.interfaces.common.helper.CommonHelper.doMap(CommonHelper.java:285) ~[classes!/:0.0.1]
at com.iwhalecloud.interfaces.common.WsClient$Companion.doParams(OhMyClient.kt:469) ~[classes!/:0.0.1]
at com.iwhalecloud.interfaces.common.WsClient$Companion.exchange(OhMyClient.kt:421) ~[classes!/:0.0.1]
at com.iwhalecloud.interfaces.common.WsClient$Companion.exchange(OhMyClient.kt:740) ~[classes!/:0.0.1]
at com.iwhalecloud.interfaces.common.WsClient.exchange(OhMyClient.kt) ~[classes!/:0.0.1]
at com.iwhalecloud.interfaces.common.bll.CommonOrderDealWorker.callRestService(CommonOrderDealWorker.java:728) ~[classes!/:0.0.1]
at com.iwhalecloud.interfaces.common.bll.CommonOrderDealWorker.doService(CommonOrderDealWorker.java:166) ~[classes!/:0.0.1]
at com.iwhalecloud.interfaces.common.bll.CommonOrderDealWorker.run(CommonOrderDealWorker.java:80) ~[classes!/:0.0.1]
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "WITH" "WITH"
at line 7, column 44.
System
- Database you are using: PostgreSQL
- Java Version: 1.8
- JSqlParser version: 4.4
Activity
manticore-projects commentedon Apr 15, 2022
Greetings.
Unfortunately you do not provide a Sample SQL statement.
I think, the challenge is not about
JSONB_ARRAY_ELEMENTS()but rather aboutWITH ORDINALITY ARR()which is not supported by JSQLParser (yet).Please provide a comprehensive SQL example first and then we will see, if we can do something about.
manticore-projects commentedon Apr 15, 2022
If I understand it correctly, it is about unnesting of functions, which produce many result rows:
"When a function in the FROM clause is suffixed by WITH ORDINALITY, a bigint column is appended to the function's output column(s), which starts from 1 and increments by 1 for each row of the function's output. This is most useful in the case of set returning functions such as unnest()."
See jOOQ/jOOQ#5799 (comment)
"The Standard specifies this clause only for the collection derived table (the UNNEST function). Db2 for z/OS, HSQLDB, and H2 also support this clause in the UNNEST; possibly some others too. But PostgreSQL also has such clause for its table functions."
qiqingli commentedon Apr 15, 2022
Sample SQL like this:
SELECT
ARR.ITEM->>'faultSystemType' AS faultSystemType,
ARR.ITEM->>'faultSystemCode' AS faultSystemCode
FROM
NET_FAULT_INFO,
JSONB_ARRAY_ELEMENTS(MAIN_FAULT_INFO) WITH ORDINALITY ARR(
ITEM,
POS
)
WHERE
ID = :pkId
manticore-projects commentedon Apr 15, 2022
Thanks for the clarification.
As suspected it is about the
WITH ORDINALITY, you can try it online here.It's not supported yet and seems to come in two flavors: Standard Compliant and PostgreSQL specific.
Would you like to send a PR or sponsor an implementation.
minleejae commentedon Sep 7, 2026
@manticore-projects The original reproducer appears resolved on master
6c726d88. I retested the complete SQL supplied in this thread, includingJSONB_ARRAY_ELEMENTS(MAIN_FAULT_INFO) WITH ORDINALITY ARR(ITEM, POS)and:pkId.Support was added in #2219. The AST now contains a
TableFunctionwithgetWithClause() == "ORDINALITY", aliasARR, and alias columnsITEMandPOS. The function argument, JSON extraction expressions and named parameter are preserved.The exact query parses with default settings and with unsupported-statement fallback disabled, and round-trips through both
toString()andStatementDeParser. Existing coverage is in TableFunctionTest.testTableFunctionWithSupportedWithClauses; the full Gradlecheckpassed as well.Could you consider closing this issue if there is no remaining reproducer on current master?