Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

ERROR: syntax error at or near "with" in jsonb_array_elements Function Call with Ordinality

I can use the with ordinality with the function jsonb_array_elements without problem when using it as a table expression – example taken from this answer

select *
from jsonb_array_elements('[{"id": 1}, {"id": 2}]'::jsonb) with ordinality as f(element, idx);

"element", idx
{"id": 1}   1
{"id": 2}   2

But when I use it as a function call I receive an ERROR: syntax error at or near "with"

with js as (
 select '[{"id": 1}, {"id": 2}]'::jsonb as props)
select  
  jsonb_array_elements(props)  with ordinality  as ch 
from js;

What is that I’m doing wrong or how can I rewrite the query to get the ordinatlity?

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

This is PostgreSQL 14

>Solution :

with ordinality can only be used if you put the function call into the FROM clause. The syntax diagram in the manual shows this.

with js as (
 select '[{"id": 1}, {"id": 2}]'::jsonb as props
)
select ch.*
from js
  cross join jsonb_array_elements(props)  with ordinality  as ch 
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading