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
