Unnest fields from a JSON/JSONB column to new columns in a PostgreSQL database table

postgres-unnest-json.R

#' Unnest fields from a JSON/JSONB column in a PostgreSQL database table
#'
#' @inheritParams dbplyr:::select.tbl_lazy
#' @param .json_col Unquoted name of the JSONB column
#' @param ... Expressions in the syntax `<new_column_name> = "<json_field_name>"`
#'
unnest_json <- function(.data, .json_col, ...) {
  .json_col <- rlang::ensym(.json_col)

  col_names <- rlang::enexprs(...)

  query_select_cols <- purrr::map(col_names, \(x) {
    dbplyr::sql_expr(
      !!.json_col %->>% !!x,
      con = .data$src$con
    )
  })

  dplyr::mutate(.data, !!!query_select_cols)
}