Hi @Piyush Narang It seems that Calcite's type inference is not perfect, and the fields of return type can not be inferred in UNNEST. (Errors were reported during the Calcite Validate phase.)
But UDTF supports this usage, and if it's convenient, you might consider writing a UDTF with similar UNNEST functions to try it out. (Use JOIN LATERAL TABLE) Best, JingsongLee ------------------------------------------------------------------ From:Piyush Narang <p.nar...@criteo.com> Send Time:2019年6月4日(星期二) 00:20 To:user@flink.apache.org <user@flink.apache.org> Subject:Clean way of expressing UNNEST operations Hi folks, I’m using the SQL API and trying to figure out the best way to unnest and operate on some data. My data is structured as follows: Table: Advertiser_event: Partnered: Int Products: Array< Row< price: Double, quantity: Int, … > > … I’m trying to unnest the products array and then compute something on a couple of fields in the product row (e.g. price * quantity) My query looks like this: SELECT partnerId, price, quantity FROM advertiser_event, UNNEST(advertiser_event.products) AS t (price, quantity, field3, field4, …) My issue / problem is that, when I try to unnest this array<row> I need to specify all the fields in the temp table as part of the unnest (“t” above). If I don’t, I get an error saying the number of fields doesn’t match what is expected. This makes my query a bit fragile in case additional fields are added / removed from this product structure. Does anyone know if there’s a way around this? As a contrast on an engine like Presto, the unnest operation would yield a ‘product’ row type which I can then use to pick the fields I want “product.price”, “product.quantity”. Presto query: SELECT partnerId, product.price, product.quantity FROM advertiser_event CROSS JOIN UNNEST(products) AS product Thanks, -- Piyush
