WITH ORDINALITY

[ArianYN]

Is WITH ORDINALTY not a feature in SQLPage?

I cannot seem to get it to work, no matter what I do...

[lovasoa]

Hello and welcome to SQLPage !
Can you please post your sql query, the error message you are getting, and the database you are using ?

[ArianYN]

I am trying to run this
JOIN LATERAL ( SELECT e.value FROM jsonb_array_elements(j1.pnet_nodes) WITH ORDINALITY AS e(value, ord) WHERE e.ord > 1 ) AS filtered_e ON true

But the error i am facing is

SQLPage found a syntax error on line 296, character 44: ) WITH ORDINALITY AS e ( value , ord ) WHERE e . ord > 1 ) AS filtered_e ON true WHERE ( j1 . pnet_nodes -> 0 ->> 'port' ) ::

sql parser error: Expected ), found: WITH at Line: 296, Column 46
SQLPage could not parse and prepare this SQL statement

[lovasoa]

Thanks for the report ! I opened an issue upstream in order to track and fix the bug in our sql parser: apache/datafusion-sqlparser-rs#1336

In the meantime, you can use row_number() to emulate the functionality:

join lateral (
  select value, row_number() over () as ord
  from jsonb_array_elements(j1.pnet_nodes)
) as filtered_e on true
where ord>1

demo

[ArianYN]

Okay, thanks 👍