удалить столбец json с помощью dbplyr и RPostgres

У меня есть столбец, содержащий строки json в таблице в базе данных PostgreSQL.

Я хочу разложить его на стороне сервера, и мне удалось выяснить код SQL для этого. Проблема, с которой я столкнулся, заключается в том, что я хотел бы иметь возможность вставить эту операцию в цепочку каналов dbplyr, т. е. обновить объект tbl_lazy и пока не запускать запрос, и я не понимаю внутренностей dbplyr< /em> достаточно для этого.

См. пример ниже:

Настройка

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)

Частичное решение (без ленивых вычислений)

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"

Желаемая функция

Я хотел бы иметь возможность сделать, например:

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"

r postgresql json dbplyr
person Moody_Mudskipper    schedule 15.03.2019    source источник

Ответы (1)


arrow_upward
3
arrow_downward

Хитрость заключается в использовании функции sql в вызове tbl.

sql следует передать строку, содержащую запрос.

Таким образом, функция становится:

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))
}

Я также изменил -> на ->> в запросе, чтобы получить правильный результат.

вывод unnest_json :

con %>%
  tbl("my_tbl") %>%
  unnest_json(bcd, unnested_b = "b", unnested_c = "c")
# # Source:   SQL [?? x 4]
# # Database: postgres [standtasic@[email protected]: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

используется в цепочке dbplyr и собирается:

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
person Moody_Mudskipper    schedule 15.03.2019