UNNEST has a surprising behavior when the argument is an array of ROW values: it will actually expand each element of the ROW into a separate column. pg_expandarray doesn't do that, it produces a single column with a ROW. That's why you can use the dot syntax to access a field.
UNNEST(ARRAY[ROW(2, 3), ROW(3, 4)] WITH ORDINALITY produces as result a table with 3 columns, the last being the ordinality: 2 | 3 | 1 3 | 4 | 2 However, the postgres function would produce a table with 2 columns, the last being the ordinality: ROW(2, 3) | 1 ROW(3, 4) | 2 You can name the columns produced by UNNEST using this syntax: UNNEST(array) WITH ORDINALITY AS R(c0, c1, o) This will produce a table with three named columns: c0 | c1 | o And then you can refer to the field you need by name. So you should be able to emulate this function if you rewrite your programs manually. I am pretty sure that with some work you can convince Calcite to accept this function using the postgres syntax. I think the trick is to parse the program using the function as a normal function call (no changes needed in the parser), then rewrite the SqlNode IR by replacing this RexCall with a call to the SqlUnnestOperator, before validating the program. Mihai ________________________________ From: Maxime Sechehaye <[email protected]> Sent: Tuesday, September 23, 2025 12:51 AM To: [email protected] <[email protected]> Subject: Re: Support for field access on a table expression Hello Mihai, Thank you for your quick response! I tried redirecting the _pg_expandarray operator to UNNEST WITH ORDINALITY, but it didn’t work—particularly when used with the dot operator. Here’s what I attempted: - Creating a custom parsing rule: didn’t work because Calcite’s default parser handles it first - Manually rewriting the query to replace _pg_expandarray with UNNEST - Implementing a custom SqlUserDefinedTableFunction _pg_expandarray whose implementation is UNNEST At the moment, I can’t think of any other solutions. Best regards, Maxime >
