Postfix function with dbplyr to extract JSON from PostgreSQL

dplyr
dbplyr
#1

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?

#2

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

#3

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:

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.