Cannot parse PGSQL JSONB_ARRAY_ELEMENTS() WITH ORDINALITY ARR()

[qiqingli]

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

[manticore-projects]

Greetings.

Unfortunately you do not provide a Sample SQL statement.
I think, the challenge is not about JSONB_ARRAY_ELEMENTS() but rather about WITH 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]

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]

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]

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.