translate `CROSS JOIN UNNEST`

[copernican]

Is there any interest in adding a dplyr-like function that implements CROSS JOIN UNNEST? I use the below frequently and am curious if it could be useful in RPresto.

cross_join_unnest <- function(x, ..., values_to = "value") {
  conn <- x$src$con
  qry <- dbplyr::build_sql(
    "SELECT a.*, ",
    dbplyr::ident(values_to),
    " FROM (",
    dbplyr::sql_render(x),
    ") a ",
    "\nCROSS JOIN UNNEST (",
    dbplyr::ident(rlang::as_name(...)),
    ") AS t(",
    dbplyr::ident(values_to),
    ")",
    con = conn
  )

  dplyr::tbl(conn, dbplyr::sql(qry))
}

This allows a workflow like

conn <- dbConnect(RPresto::Presto(), ...)
my_tbl <- dplyr::tbl(conn, "my_tbl")

my_tbl |>
  cross_join_unnest("my_array_col") |>
  select(-my_array_col) |>
  collect()

If yes, I'd be happy to create a PR.

[jarodmeng]

Interesting idea! Similar to this question raised on RPostgres. Are you planning to whip a PR on this? If yes, can you include a few different test cases (e.g. cross_join_unnest directly on a remote table vs. on a sql, etc)? I will also put the function's lifecycle as experimental. Thanks!