[feature request] Consider translation for nested tables (using dplyr, purrr syntax)

[abalter]

Implement the ability to translate verbs such as "nest", "unnest", "nest_wider", etc.

Here is a stackoverflow question discussing this: https://stackoverflow.com/questions/59163271/how-to-select-a-nested-field-with-bigrquery-using-dplyr-syntax

[hadley]

Duplicate of #252

[abalter]

@hadley -- this is about nest and unnest. Those aren't covered in #254. So is this really a duplicate?

[hadley]

That issue is about tidyr generally.

[abalter]

Understood. However, if I may be so selfish, I think "unnest" is in a different class. Nested tables are becoming very common. A large number of the public data in Google Big Query are nested. I would say that unnesting is becoming increasingly needed.

More importantly, and especially if my claim is wrong, pivot_wider and pivot_longer have dplyr workarounds. As far as I can tell, unnest does not.

The upshot is that in order to unnest a table I need to download it first, which does not work with very large tables.

Super-simple example of wide to long:

library(tidyverse)

wide = 
  data.frame(
    x = rnorm(5),
    y = rnorm(5),
    z = rnorm(5)
)

wide
#>            x          y          z
#> 1 -1.9502972  0.5407874 -1.6880831
#> 2 -0.2513272 -0.2670162 -0.2812076
#> 3  1.3028938 -0.3489442 -0.8916808
#> 4 -1.8001849 -0.5565843 -0.4271813
#> 5 -1.4801026  1.1240583  1.3784509

long = 
  union_all(
    wide %>% mutate(groups="x", values=x) %>% select(groups, values),
    wide %>% mutate(groups="y", values=y) %>% select(groups, values)
  ) %>% 
  union_all(
    wide %>% mutate(groups="z", values=z) %>% select(groups, values)
  )

long
#>    groups     values
#> 1       x -1.9502972
#> 2       x -0.2513272
#> 3       x  1.3028938
#> 4       x -1.8001849
#> 5       x -1.4801026
#> 6       y  0.5407874
#> 7       y -0.2670162
#> 8       y -0.3489442
#> 9       y -0.5565843
#> 10      y  1.1240583
#> 11      z -1.6880831
#> 12      z -0.2812076
#> 13      z -0.8916808
#> 14      z -0.4271813
#> 15      z  1.3784509

^{Created on 2020-11-25 by the reprex package (v0.3.0)}

And operations like this can be automated with functions for more flexibility. But, again, no workaround that I am aware of for unnesting