Postfix function with dbplyr to extract JSON from PostgreSQL

[mishabalyasin]

I'm trying to abstract away selecting a field from JSONB column in PostgreSQL. The syntax for the function is as follows:

(<jsonb_field> ->> '<name_of_the_field_to_extract>')::<class_of_the_object> 

The part about ->> is quite easy to achieve already with something like this:

> dbplyr::sql_expr(json_field %->>% data_field)
<SQL> json_field ->> data_field

However, I don't see a way to build the second part of the expression (::<class_of_the_object) since there is support for infix and prefix functions, but not for postfix.

Is there something I'm missing or at least some hack that I can use (other than creating strings by hand) to achieve this functionality?

[edgararuiz]

Hi, maybe a wrapper function that uses build_sql()?

[mishabalyasin]

I tried using this function, but I don't think I have a good idea of how it supposed to work.
After some more duckduckgoing, I've stumbled across this issue on GitHub:

Using PostgreSQL functions with dplyr::select

Basically, I use dplyr functions as normal and preprocess data (filtering, selecting, ...) up until the point where I need data from jsonb. I then run sql_render on resulting tbl and substitute text that selects jsonb column in SELECT ... <jsonb_field> ... with all the extractors that I prepare beforehand. Not sure if that's going to be a final solution, but it works for now.

[mishabalyasin]

For what it's worth, I've finally had an epiphany and wrote following small function that can be used for this and that I think is quite in line with dbplyr philosophy:

json_field <- function(json_column, json_field = NULL, class = NULL){
  json_column <- rlang::sym(json_column)

  rlang::quo(cast(!!json_column %->>% !!json_field %as% !!dbplyr::sql(class)))
}

I use it together with pmap/map2 to create a named list that you can splice into mutate call, so something like this:

quos <- pmap(mapping, json_field, "json_column") %>% 
   set_names(mapping[["desired_names"]])
tbl %>% 
  mutate(!!!quos)

It's more of a pseudo-code at this point, but hopefully provides enough guidance to replicate if anyone needs this.