I'm wanting to do exactly this in BigQuery, really surprised I can't in this way.
Hi @Lightbody
I hope you are well, its a good question, one I have raised internally for you, it is on the team's radar, I would also suggest raising this as an idea on the Ideas Portal too, as it would give a spotlight to other users with the same use case too.
If you need anything let me know.
Kind regards, Joe