Repository navigation
Cannot parse PGSQL JSONB_ARRAY_ELEMENTS() WITH ORDINALITY ARR() #1511
Description
Activity
Greetings.
Unfortunately you do not provide a Sample SQL statement.
I think, the challenge is not aboutJSONB_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.
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().""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."
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 = :pkIdThanks for the clarification.
As suspected it is about theWITH 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.@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?
Reacted by manticore-projects
Was expecting one of:
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "WITH" "WITH"
at line 7, column 44.
System