unnest json column with `dbplyr` and `RPostgres`

I have a column containing json strings in a table on a PostgreSQL DB.

I want to unnest it on the server side, and I was able to figure out the SQL code to do that. The challenge I'm facing is that I would like to be able to insert this operation in a dbplyr pipe chain, i.e. update the tbl_lazy object and not run the query yet, and I don't understand the internals of dbplyr enough to do that.

See example below :

Setup

library("RPostgres")
library("dplyr")

drv <- RPostgres::dbDriver("Postgres")
#### NOT REPRODUCIBLE!!! ####
con <- RPostgres::dbConnect(drv, dbname = mydbname, sslmode = 'require',
                            host = myhost, port = 5432,
                            user = user, password = mypassword)
#############################

my_tbl <- tribble(~a, ~bcd,
        1, '{"b": "foo1", "c": "bar1", "d": "baz1"}',
        2, '{"b": "foo2", "c": "bar2", "d": "baz2"}')

copy_to(con, my_tbl, "my_tbl",
        temporary = TRUE)

Partial solution (no lazy evaluation)

unnest_json <-function(data, json_col, ...){
  # build character vector whose names are cols to be created and values columns
  # to be extracted 
  dots <- sapply(as.list(substitute(list(...)))[-1], as.character)
  json_col <- as.character(substitute(json_col))
  # json extraction string
  query0  <- sprintf("%s::json->'%s' as %s",json_col, dots, names(dots))
  # complete query
  query <- sprintf("SELECT *, %s FROM (%s) AS PREV", 
                   paste(query0, collapse = ", "), 
                   dbplyr::sql_render(data))
  # fetch (when I'd rather update the tbl_lazy object instead)
  dbGetQuery(data$src$con, query)
}

con %>%
  tbl("my_tbl") %>%
  unnest_json(bcd, unnested_b = "b", unnested_c = "c")
#   a                                     bcd unnested_b unnested_c
# 1 1 {"b": "foo1", "c": "bar1", "d": "baz1"}     "foo1"     "bar1"
# 2 2 {"b": "foo2", "c": "bar2", "d": "baz2"}     "foo2"     "bar2"

Desired feature

I would like to be able to do, for instance :

con %>%
  tbl("my_tbl") %>%
  unnest_json(bcd, unnested_b = "b", unnested_c = "c") %>% # not evaluated at this point 
  select(-bcd) %>%
  head(1) %>%
  collect() 

#   a unnested_b unnested_c
# 1 1     "foo1"     "bar1"

Solution

The trick is to use the function sql into a call to tbl.

sql should be fed the string containing the query.

So the function becomes :

unnest_json <-function(.data,.json_col, ...){
  # build character vector whose names are cols to be created and values columns
  # to be extracted 
  dots <- sapply(as.list(substitute(list(...)))[-1], as.character)
  .json_col <- as.character(substitute(.json_col))
  query0  <- sprintf("%s::json ->>'%s' as %s", .json_col, dots, names(dots))
  query <- sprintf("SELECT *, %s FROM (%s) AS PREV", 
                   paste(query0, collapse = ", "), 
                   dbplyr::sql_render(.data))
  tbl(.data$src$con, sql(query))
}

I also changed -> to ->> in the query to get the right output.

ouput of unnest_json :

con %>%
  tbl("my_tbl") %>%
  unnest_json(bcd, unnested_b = "b", unnested_c = "c")
# # Source:   SQL [?? x 4]
# # Database: postgres [standtasic@adbsg@adbsg.postgres.database.azure.com:5432/standtasicdb]
#       a bcd                                                   unnested_b unnested_c
#   <dbl> <chr>                                                 <chr>      <chr>     
# 1     1 "{\"b\": \"foo1\", \"c\": \"bar1\", \"d\": \"baz1\"}" foo1       bar1      
# 2     2 "{\"b\": \"foo1\", \"c\": \"bar1\", \"d\": \"baz1\"}" foo1       bar1

used in a dbplyr chain and collected :

con %>%
  tbl("my_tbl") %>%
  unnest_json(bcd, unnested_b = "b", unnested_c = "c") %>%
  select(-bcd) %>%
  head(1) %>%
  collect()
# # A tibble: 1 x 3
#         a unnested_b unnested_c
#     <dbl> <chr>      <chr>
#   1     1 foo1       bar1